Skip to content

Why AI-generated SQL looks right and is not

Last updated

View as Markdown

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, and all of these do. Running them against the target version is what separates them.

The examples in this article come from the SQL in this library. Each one was drafted by an AI agent, reviewed by a human, published or nearly published, and later found to be wrong. The categories come from that bug list, not from hypothetical or borrowed examples.

The list is maintained: when we find a new failure mode, it gets added here.

This is the largest category because output can sound more certain than a human expert would. 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 when the claim was true.

Real examples from this library:

  • The safe way to add a NOT NULL column was taught as a four-step process: 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, so the helper CHECK constraint and the separate SET NOT NULL step go away. The old advice still works, which is why nobody caught it. It is worse advice on the exact topic where being current mattered most.
  • 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.

Watch any sentence that contains a default value, a lock level, or “since version”. Those are the facts that change between releases.

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

Section titled “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, including invalid indexes, constraint-backing indexes, and replica identity indexes, sat 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, but few people read that way for long.

3. Filters that exclude the rows you are looking for

Section titled “3. Filters that exclude the rows you are looking for”

This is a close relative of the previous failure. The query succeeds and returns 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 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?

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 failure is easy to catch because a real server rejects it immediately. It is 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

Section titled “5. Partial fixes that leave the summary contradicting the body”

This is 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. Structured data is the worst place for it to survive because assistants and search engines quote that copy, so the wrong answer outlives the corrected one.

After changing a technical claim, grep the whole file for the claim, not just the section you edited.

Longer-form generated content drifts. An article warning against repeating folklore stated a rule as absolute even though it 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 errors are invisible sentence by sentence. They appear only when you hold the thesis in mind while reading the details, which models do not reliably do across a long generation.

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

Section titled “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 real value in a document written to be pasted during an incident.

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

Reading does not work. Every defect above was reviewed by a competent human and survived, and several survived multiple rounds. 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.

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

Also assert two things 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 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

Section titled “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, and a better prompt is not enough. Give the agent the real schema and the real version instead of letting it infer them. Execute what it writes somewhere consequences are cheap before executing it somewhere they are not.

  • 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 the correct answer, a missing predicate, a filter that excludes the rows you were looking for, a column that exists on a different major version. Reviewers check whether SQL looks reasonable; these failures all look reasonable. Executing the query against the target version is what separates them.

  • What is the most common AI SQL error in PostgreSQL?

    Stale version claims. A model trained largely on material about older releases will confidently state defaults and behaviour that a recent major version changed, and state them without hedging, because the training data did not hedge. PostgreSQL 18 changed NOT NULL constraint handling, made BUFFERS implicit in EXPLAIN ANALYZE, and added catalog columns; advice written from PostgreSQL 15 habits is wrong in ways that still parse and still run.

  • How do I catch bad AI-generated SQL before it ships?

    Execute it, against every major version you claim to support. Most of these defects are invisible to reading and immediate under execution: a catalog column that does not exist errors instantly, a version-gated syntax fails on the older release, a filter that excludes the target rows returns an empty set on a fixture built to contain them. A CI job that runs each query against a version matrix converts a review problem into a test failure.

  • Can I trust an AI agent to write catalog queries?

    Draft them, yes. Ship them unexecuted, no. Catalog queries are the worst case for a language model: pg_index, pg_class, and pg_stat_* have many similarly-named boolean columns, the correct set of filters is workload-specific, and an incomplete filter returns a plausible result rather than an error. An unused-index query missing one predicate still returns a tidy list, it just tells you to drop something you must not drop.