# 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 →](https://app.dbgorilla.com/signup)

## 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.

## Related

- [Why is my AI-generated SQL slow?](https://www.dbgorilla.com/learn/postgres/why-ai-generated-sql-is-slow/)
- [Why does my AI agent hallucinate columns that do not exist?](https://www.dbgorilla.com/learn/postgres/why-does-my-ai-agent-hallucinate-columns-that-dont-exist/)
- [Is it safe to let an AI agent access my production database?](https://www.dbgorilla.com/learn/postgres/is-it-safe-to-let-an-ai-agent-access-my-production-database/)
- [How to find missing indexes in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)