You ask an AI assistant for "total revenue by customer last month," it hands back a clean-looking query, you run it, and Postgres throws:
ERROR: column "c.total_revenue" does not exist
LINE 3: SUM(c.total_revenue) AS revenue
The query was confident. It was syntactically perfect. It was also referencing a column that has never existed in your schema. Welcome to the single most common failure mode of AI-generated SQL: the schema hallucination.
If you've spent any time using LLMs to write queries, you've hit this. The model invents a total_revenue column, joins to an accounts table you don't have, or assumes your orders table has a status column when yours calls it state. It's frustrating precisely because everything looks right. In this article I'll explain why this happens under the hood, and give you a concrete playbook to stop it — whether you're pasting schemas into ChatGPT or building a text-to-SQL feature into your own product.
Why AI hallucinates tables and columns
Large language models don't "know" your database. They generate SQL the same way they generate prose: by predicting the most statistically likely next token based on patterns seen during training. When you ask for revenue, the model has seen thousands of tutorials with a total_revenue column, so it confidently reaches for that name — even though your actual schema stores it as amount_cents on an invoices table.
There are a few distinct root causes worth naming, because each has a different fix:
| Cause | What it looks like |
|---|---|
| No schema grounding | The model never saw your real tables, so it guesses common names |
| Training-data over-generalization | It reaches for "textbook" column names like created_date instead of your created_at
|
| Underspecified questions | Vague prompts ("show me active users") invite the model to invent an is_active flag |
| Complex/ambiguous design | Multiple tables with similar columns confuse which one the model should use |
The important insight: a hallucinated column is syntactically valid but semantically misaligned with your actual schema. The model isn't broken — it's doing exactly what it was designed to do with insufficient information. Which means the fix is almost always about feeding it better context, not about finding a smarter model.
Fix #1: Ground the model in your real schema
The number one cause of hallucinations is that the model is guessing at names it was never told. So tell it. Instead of asking "write me a query for revenue by customer," give it the actual DDL:
-- Paste this before your question
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
company_name TEXT,
created_at TIMESTAMPTZ
);
CREATE TABLE invoices (
id BIGINT PRIMARY KEY,
customer_id BIGINT REFERENCES customers(id),
amount_cents INTEGER,
status TEXT, -- 'paid', 'open', 'void'
issued_at TIMESTAMPTZ
);
With that in context, the model now produces:
SELECT
c.company_name,
SUM(i.amount_cents) / 100.0 AS revenue_dollars
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.status = 'paid'
AND i.issued_at >= date_trunc('month', now()) - interval '1 month'
AND i.issued_at < date_trunc('month', now())
GROUP BY c.company_name
ORDER BY revenue_dollars DESC;
Notice it used amount_cents and issued_at — your real column names — because you removed the guesswork. If you're building this into an app, you can pull the schema programmatically:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;
Feed that output into your prompt as structured context and hallucination rates drop dramatically.
Fix #2: Add comments and metadata for ambiguous columns
Column names are often cryptic, and a model can't tell that state means order status versus a US state. Annotate them. Comments in the DDL you provide double as documentation the model reads:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
state TEXT, -- order lifecycle: 'cart','placed','shipped','delivered','refunded'
total INTEGER,-- order total in cents, NOT dollars
placed_at TIMESTAMPTZ
);
The -- order total in cents comment alone prevents the classic bug where the model forgets to divide by 100. For ambiguous business terms — what counts as an "active" customer, how "MRR" is calculated — spell out the definition. The model can't infer your business logic; it can only work with what you give it.
Fix #3: Use a semantic layer instead of raw tables
For a production text-to-SQL feature, exposing raw tables to an LLM is asking for trouble. A better pattern is a semantic layer: a curated set of views or defined metrics that map messy physical tables to clean business concepts. The model maps the user's intent onto this safe, well-named layer instead of navigating raw schema.
CREATE VIEW revenue_by_customer AS
SELECT
c.id AS customer_id,
c.company_name,
date_trunc('month', i.issued_at) AS month,
SUM(i.amount_cents) / 100.0 AS revenue
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.status = 'paid'
GROUP BY 1, 2, 3;
Now a question like "revenue for Acme last month" maps to a trivially correct query against revenue_by_customer. There are fewer tables, cleaner names, and no room to hallucinate a join. As a bonus, the view enforces your business rules (only paid invoices count) so the AI can't accidentally include voided ones.
Fix #4: Give the model examples (few-shot prompting)
Models are far more accurate when shown a couple of correct question-to-SQL pairs from your schema. This is called few-shot prompting, and research on approaches like DAIL-SQL shows curated examples meaningfully improve accuracy. Include two or three representative pairs in your prompt:
Q: How many orders did we ship yesterday?
A: SELECT COUNT(*) FROM orders
WHERE state = 'shipped'
AND placed_at::date = current_date - 1;
Q: Top 5 customers by revenue this year?
A: SELECT company_name, revenue
FROM revenue_by_customer
WHERE month >= date_trunc('year', now())
ORDER BY revenue DESC LIMIT 5;
These examples teach the model your naming conventions, your preferred date handling, and which views to prefer — all without a single fine-tuning run.
Fix #5: Validate before you run
Even with perfect grounding, treat generated SQL as untrusted input. The cheapest safety net is to validate column and table references against the real schema before execution. In Postgres you can dry-run without touching data using EXPLAIN:
EXPLAIN SELECT c.company_name, SUM(i.total_revenue) -- hallucinated column
FROM invoices i JOIN customers c ON c.id = i.customer_id
GROUP BY c.company_name;
-- ERROR: column i.total_revenue does not exist
If EXPLAIN fails, you've caught the hallucination without running anything. Feed the error message back to the model and let it self-correct — this validate-and-retry loop is one of the most effective mitigations in practice. Always run generated queries as a read-only role so a hallucinated DELETE or DROP can never do damage.
Common gotchas
| Gotcha | How to avoid it |
|---|---|
| Stale schema in the prompt | Regenerate schema context on each run; don't hardcode it once |
| Silent wrong answers | A query can be valid SQL yet use the wrong column — spot-check results against known numbers |
| Cents vs. dollars | Document units in column comments; it's the most common "looks right, is wrong" bug |
| Ambiguous joins | Provide foreign keys in the DDL so the model doesn't invent join conditions |
| Over-trusting big models | A smarter model hallucinates less, but never zero — keep the validation layer |
The trickiest failures aren't the ones that error out — those are easy. The dangerous ones are queries that run cleanly but quietly use the wrong table or forget a WHERE filter, returning a plausible-but-wrong number that ends up in a dashboard. That's why validation and result spot-checking matter as much as grounding.
Key takeaways
Schema hallucination happens because the model is pattern-matching against training data instead of your actual database. The fix is context, not a bigger model. Ground every prompt in your real DDL, annotate ambiguous columns with comments, expose a clean semantic layer of views rather than raw tables, show the model a few correct examples, and always validate generated SQL against the schema before running it as a read-only user. Do those five things and "column does not exist" mostly disappears from your life.
Have you built text-to-SQL into a product, or are you still copy-pasting schemas into a chat window? What's tripped you up the most — hallucinated columns, wrong joins, or the silent-wrong-answer problem? Drop your war stories in the comments. And if you've found a grounding or validation trick that works, I'd love to hear it.
Sources: Reducing Hallucinations in Text-to-SQL (Wren AI), Deep Dive into Text-to-SQL Hallucinations (DataFocus), Improving Text-to-SQL Accuracy with Schema-Aware Reasoning (Towards AI), Before Generation, Align it! (arXiv)
Top comments (1)
Great write-up. I’d add one more layer for production systems: schema grounding tells the model what can be queried, but authorization should determine what may be queried. Even a perfectly grounded model shouldn’t see every table or column. Building the schema context from the user’s effective permissions (or a semantic layer filtered by role) not only reduces hallucinations but also prevents accidental data exposure. In production text-to-SQL, grounding and authorization should go hand in hand.