Skip to main content

Why AI-generated SQL looks right and is not

Quick answer: AI-generated SQL fails in a small number of repeatable ways, and none of them are syntax errors. The query parses, reads fluently, and looks like the correct answer: a version claim asserted from stale training data, a paragraph describing a filter the query never applies, a catalog column that sounds real and is not, a fix applied to the body but not the summary. Reviewers check whether SQL looks reasonable. All of these look reasonable. Running them against the target version is what separates them.

This article is written from a specific, unflattering source: the SQL in this library. Everything below is a defect that was drafted by an AI agent, reviewed by a human, published or nearly published, and later found to be wrong. The taxonomy is not hypothetical and it is not borrowed. It is a bug list.

It is also maintained. When we find a new failure mode, it gets added here.

1. Stale version claims​

The single largest category, and the most dangerous, because the output is more confident than a human expert would be. An engineer who last used PostgreSQL 15 daily will say "I think this changed recently, let me check." A model reproducing the same knowledge says it flatly, because the text it learned from was written flatly at the time it was true.

Real examples from this library:

  • The safe way to add a NOT NULL column was taught as a four-step dance: nullable column, backfill, validated CHECK, SET NOT NULL. Correct through PostgreSQL 17. PostgreSQL 18 lets you add the not-null constraint itself as NOT VALID and validate it under SHARE UPDATE EXCLUSIVE, which is two steps and removes the ACCESS EXCLUSIVE scan. The old advice still works, which is why nobody caught it. It is simply worse, on the exact topic where being current was the whole value.
  • BUFFERS was presented as something you add to EXPLAIN (ANALYZE). From PostgreSQL 18 it is on by default, and the sample plan output we published no longer matched what a reader would actually see.
  • A checkpoint query was labelled "PostgreSQL 17+" while selecting num_done, a column added in 18. On 17, the version the comment specifically advertised, the view exists and the column does not.

The tell: any sentence containing a default value, a lock level, or "since version". Those are exactly the facts that move.

2. The prose says one thing, the query does another​

An AI-drafted section explains the reasoning correctly and then emits a query that does not implement it. This survives review because reviewers read the explanation, agree with it, and skim the SQL for shape.

Our worst instance: a paragraph listing the indexes you must exclude before dropping anything, invalid indexes, constraint-backing indexes, replica identity, sitting directly above a query that selected indisvalid into the output and never filtered on it. The prose was right. The query silently returned the very indexes the prose told you to rule out.

A reviewer checking "does the explanation make sense?" passes this. A reviewer checking "does this WHERE clause contain every predicate the paragraph promised?" catches it, and nobody reads that way for long.

3. Filters that exclude the rows you are looking for​

A close relative, and the most insidious, because the query succeeds. No error, no empty result, just a plausible answer that is missing the case you were hunting.

The example we shipped: a transaction-ID wraparound diagnostic filtered to relkind IN ('r', 'm'), ordinary tables and materialised views. TOAST relations have relkind = 't'. They carry their own relfrozenxid, and because TOASTed rows are the ones that rarely get updated, the TOAST relation is frequently the oldest thing in the database. So a query written to find what is driving an anti-wraparound vacuum structurally could not return the answer, and reported a comfortable age while the real one climbed.

Anything that filters a catalog view by type, kind, schema, or namespace deserves the question: what does this exclude, and is the thing I'm looking for in the excluded set?

4. Plausible-but-nonexistent identifiers​

Models generate pg_stat_user_indexes.last_idx_scan because it is exactly what the column would be called. It is real, from PostgreSQL 16. Before that, the query errors.

This is the friendliest failure mode in the family, because a real server rejects it immediately. It is only dangerous when nobody runs the query, which is precisely the situation this article is about.

5. Partial fixes that leave the summary contradicting the body​

The failure mode of correcting AI-generated content, and the one we repeated most often. An article carries the same claim in five places: the frontmatter description, the Quick Answer, the body, the visible FAQ, and the JSON-LD that search engines actually read. A fix gets applied to the body. The other four keep saying the old thing.

We did this three separate times, including once on autovacuum hours after flagging the pattern. The structured data is the worst place for it to survive, because that is the copy assistants and search engines quote, so the wrong answer outlives the corrected one.

If you take one operational habit from this page: after changing a technical claim, grep the whole file for the claim, not just the section you edited.

6. Advice that undercuts its own argument​

Longer-form generated content drifts. An article warning against repeating folklore stated a rule as absolute that was never absolute. A section explaining sargability recommended wrapping the indexed column in AT TIME ZONE, which destroys sargability, the exact failure the section was teaching readers to avoid.

Both are invisible sentence-by-sentence. They are only visible if you hold the thesis in mind while reading the details, which is what a model does not do across a long generation.

7. Placeholders that are safe to read and unsafe to run​

Generated runbooks emit concrete-looking values because concrete examples read better. We published SELECT pg_replication_slot_advance('cdc_slot', '0/A0000000');, a real command with a made-up LSN. Advancing a slot discards every change between its current position and the target, permanently. The value looked like a value, in a document written to be pasted from during an incident.

Anything destructive should be un-runnable as printed, not merely commented.

How do I catch these?​

Reading does not work. Every defect above was reviewed by a competent human and survived, and several survived multiple rounds. The common property is that they are invisible to inspection and immediate under execution.

So execute them, against every major version you claim to support:

  • A nonexistent column errors instantly.
  • Version-gated syntax fails on the older release, which also proves your version note, rather than assuming it.
  • A filter that excludes your target rows returns nothing, against a fixture built to contain exactly those rows.

That last point matters: the fixture should contain the pathological case. A TOAST relation with an old relfrozenxid, an invalid index, an index backing a replica identity. A test database full of well-behaved rows agrees with a broken query.

Two things worth asserting beyond "it runs":

Assert version boundaries in both directions. A block marked "PostgreSQL 18+" should fail on 17. If it passes, the version note is unearned, which is its own kind of wrong, and one that quietly teaches readers to distrust the real notes.

Require a reason for every skip. Some SQL genuinely cannot run in CI. Make that an explicit, printed annotation rather than a silent omission, or coverage erodes to nothing without anyone deciding it should.

What this means for AI agents on your database​

The pattern generalises past documentation. An agent writing a migration, a diagnostic query, or a cleanup script produces the same four failure shapes: current-sounding claims from stale training data, filters that do not match the stated intent, plausible identifiers, and confident output where a human would hedge.

The mitigation is the same too, and it is not a better prompt. It is giving the agent the real schema and the real version instead of letting it infer them, and executing what it writes somewhere consequences are cheap before executing it somewhere they are not.

How DBGorilla helps​

DBGorilla connects to your database read-only and works through the AI coding agent you already use (Claude Code, Cursor) over MCP. It gives the agent the facts it otherwise guesses at, your actual server version, your actual schema, your actual index and statistics data, so the version claims and column names in its output come from your database rather than from its training data. It surfaces and explains; it does not run migrations, change configuration, or write to your data. Get started free →

Frequently asked questions​

Why does AI-generated SQL pass review but fail in production? Because the failures are not syntax errors. The query parses, reads fluently, and matches the shape of a correct answer. Reviewers check whether SQL looks reasonable; these all look reasonable. Execution against the target version is what separates them.

What is the most common AI SQL error in PostgreSQL? Stale version claims, defaults, lock levels, and "since version" statements asserted without hedging, because the training data did not hedge when it was written.

How do I catch bad AI-generated SQL before it ships? Run it, against every major version you support, with fixtures that contain the pathological cases. Most of these defects are invisible to reading and immediate under execution.

Can I trust an AI agent to write catalog queries? To draft, yes. To ship unexecuted, no. Catalog views have many similarly-named boolean columns, and an incomplete filter returns a plausible list rather than an error, which is worse than failing.