Skip to content

Why does my AI coding agent hallucinate columns and tables that do not exist?

Last updated

View as Markdown

Quick answer: Because it has never queried your database. Claude Code and Cursor read your repository, so they infer a plausible schema from your code, your naming conventions, and patterns in their training data, then write SQL against that guess. The result is idiomatic, review-passing SQL that fails at runtime with column does not exist. The fix is to let the agent read the live catalog.

What is actually happening?

Section titled “What is actually happening?”

An AI coding agent is doing pattern completion over everything it can see. What it can see is your source code. What it cannot see is your database.

So when you ask for “a query that lists inactive users with their last order date,” the model assembles a schema from three sources:

  1. Your code. ORM models, migration files, type definitions, existing queries, GraphQL schemas. Real signal, but it is the schema your code thinks exists.
  2. Naming conventions. If your tables are snake_case and plural. It will generate snake_case plural names. If it sees user_id in one place it will assume user_id everywhere.
  3. Training data. It has read an enormous number of applications. Most of them have created_at, updated_at, deleted_at, is_active, email, status. These are prior probabilities, and they are strong.

None of those sources is information_schema. The model has never run a query against your database in its life. So when your table happens to use signup_ts instead of created_at, or archived_at instead of deleted_at, or the soft-delete flag lives on a join table rather than the main one, the agent generates the statistically likely answer, and it is wrong.

A useful mental model: it is not lying. It is interpolating. Given everything it has seen, created_at is a very good bet. It is just not a fact.

Why is this failure mode so dangerous?

Section titled “Why is this failure mode so dangerous?”

Most model errors announce themselves. This one does not.

The SQL looks right. It uses your table names, your casing, your join style. It reads like something a competent engineer on your team wrote. There is nothing to catch in review unless the reviewer happens to have the actual column list memorised.

Type checking usually does not save you. Raw SQL strings are opaque to most type systems. Even in ORMs, a hallucinated column often appears in a where clause or a raw fragment that is not statically checked against the database.

It fails late. Postgres raises the error at execution, not at deploy. If the query is on a rare code path, an admin report, an error handler, a monthly job. It can sit in the codebase for weeks and then fail in production, in the path you least wanted to be broken.

Trust breaks at the worst moment. The failure mode of a tool that is right 95% of the time and confidently wrong the other 5% is that you stop being able to use its output without verification, which erases much of the speed benefit you adopted it for.

The related and worse version: a hallucinated column in a WHERE clause that happens to exist on the table but means something different from what the agent assumed. That does not error at all. It returns wrong results, quietly.

Why do not the usual workarounds hold up?

Section titled “Why do not the usual workarounds hold up?”

Pasting DDL into the prompt

Section titled “Pasting DDL into the prompt”

Works, for that conversation. You paste CREATE TABLE statements for the three tables involved and the agent produces correct SQL.

It fails on scope and repetition. You paste the tables you think are involved, so the agent still guesses at the fourth one it needed. And you do it again next session, and the session after. It does not compound.

A schema dump in CLAUDE.md or .cursorrules

Section titled “A schema dump in CLAUDE.md or .cursorrules”

The natural next step, and the one that causes the most damage.

The instinct is right: put real facts where the agent will always see them. The implementation has no refresh mechanism. You commit a schema dump on Tuesday, run four migrations over the next month, and the file is now a confident, detailed, authoritative description of a database that no longer exists.

A stale schema file is often worse than no schema file. Without one, the agent guesses, and a guess carries some implicit uncertainty, the agent may hedge, may ask, may write defensively. With a stale file, the agent has been handed something that presents as ground truth. It will not hedge. It will write SQL against your February schema with total confidence, and so will you when you review it.

If you keep a schema file anyway, make regenerating it part of your migration process, a script or CI step that rewrites it on every schema change. A file that is regenerated automatically is a cache. A file that is regenerated manually is a liability.

ORM model files as the source of truth

Section titled “ORM model files as the source of truth”

Better than a hand-written dump, because they at least live next to the code and change with it. But ORM models describe what the application expects, not what is in the database. They drift in specific, common ways:

  • A migration ran that the models were never updated for (or vice versa, a model field added in code before the migration shipped).
  • Columns added directly in production during an incident.
  • Views, materialized views, and columns created by extensions or other services that the ORM never modelled at all.
  • Multiple services writing to the same database, each with its own partial model layer.
  • Indexes and constraints, which most ORM model files describe incompletely, so the agent cannot tell whether the column it is filtering on is indexed.

The database is the only thing that knows what the database contains.

How do I let the agent read the real schema?

Section titled “How do I let the agent read the real schema?”

Query the catalog. These are all read-only and cheap, and they are what you want a connected agent running instead of inferring.

Columns and types for a table:

SELECT
  column_name,
  data_type,
  is_nullable,
  column_default,
  character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
ORDER BY ordinal_position;

One caveat that matters precisely because you are using a narrowly-scoped role: information_schema.columns only shows columns the current user has some privilege on. A table the agent’s role was not granted SELECT on will not error. It will simply be absent, and the agent will conclude it does not exist. That is the same hallucination-by-missing-fact this article is trying to eliminate, now wearing an authoritative-looking source.

The fix is not to grant SELECT on everything. SELECT is a data privilege; granting it so the agent can discover a column name also hands it every row in that table, which is a much larger concession than the problem requires. Read pg_catalog.pg_attribute joined to pg_class instead. It is not privilege-filtered, so the agent gets complete structural metadata (names, types, nullability) without gaining access to any data. Keep an explicit schema allowlist on those catalog queries, and widen SELECT only where you actually intend the agent to read rows.

Every table and its column count, for orientation:

SELECT
  c.relname AS table_name,
  count(a.attname) AS columns,
  NULLIF(c.reltuples, -1)::bigint AS approx_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_attribute a
  ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
WHERE c.relkind IN ('r', 'p', 'm')
  AND n.nspname = 'public'
GROUP BY c.relname, c.reltuples
ORDER BY approx_rows DESC NULLS LAST;

Primary keys, unique constraints, and foreign keys. This is the part ORM models describe worst, and it is what an agent needs to write a correct join:

SELECT
  con.conname     AS constraint_name,
  con.contype     AS type,          -- p = primary, u = unique, f = foreign, c = check
  rel.relname     AS table_name,
  pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = 'public'
ORDER BY rel.relname, con.contype;

pg_get_constraintdef() gives you the full definition as text, including which column references which table, which is exactly the shape an agent can reason over.

Indexes, so the agent knows what is actually cheap to filter on:

SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Enum values, a quiet source of hallucinated string literals:

SELECT
  t.typname AS enum_type,
  e.enumlabel AS value
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE n.nspname = 'public'
ORDER BY t.typname, e.enumsortorder;

An agent that guesses status = 'canceled' when your enum says 'cancelled' produces SQL that runs fine and returns zero rows forever.

How do I stop it coming back?

Section titled “How do I stop it coming back?”

Make the live catalog the source of truth. Whatever the mechanism, a read-only MCP connection, a tool the agent can call, the goal is that the schema the agent reasons over is fetched at the moment it is needed, not copied at some point in the past.

If you keep a schema file, generate it. A CI step or migration hook that rewrites it on every schema change turns a liability into a cache. Fail the build if the committed file does not match a fresh dump.

Catch it in CI, not in production. Run the queries your code generates against a database restored from a real schema, in tests. A hallucinated column fails immediately there, which is the whole point.

Ask the agent to verify before it writes. If it has catalog access, “check the actual columns on that table before writing the query” is a cheap instruction that materially changes output quality, it converts a guess into a lookup.

Treat “the agent is confident” as no evidence at all. Confidence in a generated answer reflects how typical the answer is, not whether it is true of your database.

FAQ

Section titled “FAQ”
  • Why does my AI agent invent column names?
    It has never seen your schema. It infers a plausible one from your code, your naming conventions, and patterns across the enormous number of applications in its training data, so it confidently produces created_at, user_id, and deleted_at whether or not your tables have them.
  • Why is a hallucinated column worse than an obvious mistake?
    It is invisible until runtime. The SQL is valid and idiomatic, so it passes review and most type checks, then fails with column does not exist, often in a rare code path that only breaks in production. The variant where the column exists but means something else does not error at all; it silently returns wrong rows.
  • Should I paste my schema into CLAUDE.md or .cursorrules?
    Only with a mechanism to regenerate it. A committed dump has no expiry and stops matching reality after the next migration. A stale schema file is often worse than none, because it makes the agent confidently wrong instead of appropriately uncertain.
  • Are my ORM models not good enough?
    They describe what the application expects, not what the database contains. They drift when migrations and models get out of sync, when columns are added directly in production, when other services write to the same database, or when views and extension-created objects were never modelled. They also describe indexes and constraints poorly.
  • How do I let an AI agent read my real database schema?
    Give it a read-only connection and let it query the catalog: information_schema.columns for columns and types, pg_constraint with pg_get_constraintdef() for keys and foreign keys, pg_indexes for indexes, and pg_enum for allowed enum values.
Section titled “Related”