DuckDB's New Agent Mode In v2.0

With “AI” agents now wielding SQL queries as or more often than humans do, the makers of DuckDB decided to create an output mode more suitable for our forthcoming overlords. Agents/LLMs work best if there is crisp context, and the output modes of the DuckDB CLI were originally designed for either human consumption, or to create data files. For humans, it pads every column so the borders line up, prints the column types in a second header row, and draws a box around the result. When that gets sent into a model’s context you literally pay for every one of those meaningless padding spaces and border characters (unless you’re using a local model). Worse, when a result is large the CLI shows the first and last rows with a row of dots in the middle, which may cause weaker models to generate a confident conclusion from data it never saw.

DuckDB v2.0 fixes this with an agent mode. The DuckDB team announced it this week. As regular readers know all-to-well, I run a daily observatory that pipes Caddy logs into DuckDB, so I pointed the new binary at 17,834 lines of real traffic from this site and measured the difference. The short version: about 75% less text to read, and no more silent truncation.

Agent mode redux

Agent mode is automatic. It switches on when three things are true at once:

1. Your tool sets one of a known list of environment variables: AGENT, AI_AGENT, CLAUDECODE, CODEX_*, CURSOR_AGENT, GEMINI_CLI, or COPILOT_* (I confirmed oh-my-pi/omp does this.)
2. Standard output is not a terminal. Agents capture output through a pipe, so this is already true.
3. You did not ask for a specific output format.

That third rule matters. If you pass -csv, -json, or -markdown, you asked for exactly that, and agent mode steps aside. You can override either way with -agent and -no-agent.

GLP-1 for DuckDB output

I ran three queries against the log file, once in each mode:

At roughly four bytes per token, the user-agent query drops from about 1,665 tokens to about 415. The savings grow as the result gets wider (thanks to the aforementioned padding). Agent mode prints a markdown table with the type folded into each header cell:

| ua:VARCHAR | hits:BIGINT | ips:BIGINT |
|---|---|---|
| Feedly/1.0 (poller; 2 subscribers; ) | 938 | 3 |
| curl/8.7.1 | 719 | 3 |

The | characters carry the structure, and the output is still valid markdown, so a human reading the agent’s transcript gets a rendered table instead of a wall of dots.

Truncation all I never wanted

The old renderer shows a large result as its first and last rows around a row of dots, then a footer like 1851 rows (40 shown).

Agent mode keeps the sample but makes the gap loud and explicit:

| /conf/.env | 308 | 1 |
first 20 and last 20 of 1851 rows (.maxbytes 0 for all), hash 565a386b262d47e1

The footer shows the real total, tells you how to get all of the results, and ends with a hash.

That hash covers every row, including the ones past the cut, and it does not depend on row order. So an agent that wants to know whether a result changed after an edit can compare two footers instead of printing two full results. Running the same aggregate twice gives the same hash:

run 1: 15 rows, hash 71316dfd97b2469a
run 2: 15 rows, hash 71316dfd97b2469a

Change the filter and the hash also changes:

status=200: 15 rows, hash cc7b6be9cfd0be44

The hash also treats NULL differently from the string 'NULL', so a data-cleaning bug cannot hide inside it.

To err in JSON

When a query fails, the error goes to standard error as JSON instead of a formatted block:

{"exception_type":"Binder","exception_message":"Referenced column \"nope\" not found in FROM clause!\nCandidate bindings: \"logger\", \"size\"","error_subtype":"COLUMN_NOT_FOUND","location":"[7,4]","position":"7","name":"nope"}

An agent can read error_subtype and exception_message as fields instead of scraping text with a regular expression.

Cost estimates and compact plans

Two more changes help an agent decide what to do before it commits to waiting. First, when the planner expects a statement to read a million rows or more, it prints an estimate on standard error before execution starts:

estimate: ~50000000 rows read (range ~50000000), ~1 rows returned

An agent that sees that number can choose to wait, or to interrupt and rewrite the query. Second, EXPLAIN now defaults to a compact, one-operator-per-line format:

HASH_GROUP_BY (est=632) Groups: #0; Aggregates: count_star()
  PROJECTION (est=1000) Projections: region
    RANGE (est=1000) Function: RANGE

Gotchas

• -csv, -json, and -markdown turn agent mode off. That is deliberate: a format flag is a request for exactly that format.
• A result prints in full up to 1,000 rows and 10,000 bytes. Past that you get the sample. Widen it with .maxrows N and .maxbytes N (-1 or 0 means no limit).
• A query that produces more than 100,000 extra rows is stopped, and the count is reported as a lower bound (at least N rows).
• .tables prints one line per table with an approximate row count and the columns, but the command has to sit on its own line to run.

FIN

We’ll see how much this improves context handling and reduces query retries over the coming weeks. I’d been holding off testing v2.0, but this new feature provided a compelling reason to finally give it a go.