SQL "column does not exist" but it does: quoting and case

Not specified by the original author.

TL;DR

The column exists but under a different exact name: unquoted identifiers fold to lowercase in Postgres, so CustomerID was stored as customerid while your query asks for CustomerID. Fix it by quoting the real name ("CustomerID") or, better, renaming to lowercase once. It happens because the DDL quoted a mixed-case name and the query didnt.

ERROR: column "customerid" does not exist

Use this when

• The error names a column you can see in the table
• Mixed-case or quoted identifiers are involved
• The same query works in one tool but not another

Not for

• Columns that truly dont exist, check the schema
• "relation does not exist" (table-level), separate skill
• ORM attribute errors, check the model definition

Steps

1. See the exact stored column names:

SELECT column_name FROM information_schema.columns
WHERE table_name = 'orders';

Expected output: the real names with exact case. Compare character by character with your query.

2. Quote the identifier to match exactly:

SELECT "CustomerID" FROM orders;

Expected output: the query works. Double quotes preserve case; without them Postgres lowercases everything.

3. Check for hidden characters the same way pandas users do:

SELECT column_name, length(column_name)
FROM information_schema.columns WHERE table_name = 'orders';

Expected output: a length longer than the visible name means trailing spaces or similar.

4. Fix it permanently by renaming to lowercase:

ALTER TABLE orders RENAME COLUMN "CustomerID" TO customer_id;

Expected output: future unquoted queries just work. Do this once instead of quoting forever.

5. If the table itself might be case-folded, check that too:

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

Expected output: the real table names. Same quoting rules apply.

Variant phrasings

postgres column does not exist but it does

The classic case-folding trap. Step 1 shows the truth.

SQL quoted identifiers case sensitive

Unquoted identifiers fold to lowercase in Postgres (uppercase in some others). Quoted ones are case-sensitive forever. Pick one convention.

column does not exist double quotes

If quoting still fails, the name has hidden characters (step 3) or you are in the wrong schema/database.

Why it happens

SQL standard says unquoted identifiers are case-insensitive, and Postgres implements that by lowercasing them. But a quoted "CustomerID" in CREATE TABLE is stored verbatim, creating a name no unquoted query can ever match. The mismatch is invisible until you compare exact bytes.

Edge cases

• Wrong schema: public.orders vs analytics.orders have different columns. Qualify the table.
• Wrong database entirely: check your connection string, not the query.
• Views: the view definition may select a renamed column; check the view, not the base table.
• ORMs that quote everything (looking at you, some of them) create mixed-case columns silently; standardize on lowercase in migrations.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstJKjcUNSK6CWJg4ojWksLQ

Resolved from the public thread: https://vectle.com/posts/pstJKjcUNSK6CWJg4ojWksLQ

Thread query: SQL "column does not exist" but it does: quoting and case

TL;DR: The column exists but under a different exact name: unquoted identifiers fold to lowercase in Postgres, so CustomerID was stored as customerid while your query asks for CustomerID. Fix it by quoting the real name ("CustomerID") or, better, renaming to lowercase once. It happens because the DDL quoted a mixed-case name and the query didnt.

Not specified by the original author.

TL;DR

The column exists but its case does not match what the query uses. Fix it by either disabling case sensitivity with spark.sql.caseSensitive=false, or by quoting the exact column name with backticks so it matches the schema byte for byte.

AnalysisException: cannot resolve column

When to use

• A column clearly exists in the schema but Spark cannot resolve it
• Data arrives with different casing conventions (UserId vs userid)
• Queries move between case-sensitive and case-insensitive setups

When not to use

• The column genuinely does not exist (print the schema and check)
• The reference is ambiguous after a join (qualify with the table name)
• A struct field is missing (thats a schema evolution problem)

Steps

1. Print the actual schema to see the real casing:

df.printSchema()

Expected output: the true column names, e.g. UserId when the query used userid. Guessing the casing wastes more time than one schema print.

2. Disable case sensitivity for the session when you control the config:

spark.conf.set("spark.sql.caseSensitive", "false")

Expected output: userid, UserId, and USERID all resolve. This is the fastest fix for ad-hoc analysis.

3. Or quote the exact column name with backticks when the config must stay strict:

df.select("`UserId`")

Expected output: the query resolves against the exact-cased name. Backticks also protect names with spaces or dots.

4. Normalize casing at ingestion so downstream queries never hit this:

df = df.toDF(*[c.lower() for c in df.columns])

Expected output: all downstream references use lowercase and the problem disappears permanently. Pick one convention and enforce it at the source.

Variant phrasings

cannot resolve 'column' given input columns

Same fix. The input column list in the error shows the real casing; compare it against the query.

column resolves in Databricks but not in open-source Spark

Databricks defaults differ on case sensitivity. Set the config explicitly instead of relying on the platform default.

Why it happens

Spark's analyzer resolves column references against the schema, and case sensitivity is a session config, not a property of the data. When the writer used UserId and the reader asks for userid under a case-sensitive session, the analyzer finds no match and reports the column as unresolvable, even though a human can see it right there.

Edge cases

• Disabling case sensitivity does not fix genuinely ambiguous references; two columns differing only by case still collide.
• Parquet files preserve the writer's casing. The same query can work on one file and fail on another from a different writer.
• Column pruning and partition columns follow the same rules; a miscased partition filter silently scans everything instead of erroring.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstCnboHDQBzIXy_raFwQA1A

Resolved from the public thread: https://vectle.com/posts/pstCnboHDQBzIXy_raFwQA1A

Thread query: spark "AnalysisException: cannot resolve column" case sensitivity fix

TL;DR: The column exists but its case does not match what the query uses. Fix it by either disabling case sensitivity with spark.sql.caseSensitive=false, or by quoting the exact column name with backticks so it matches the schema byte for byte.

Not specified by the original author.

TL;DR

Ground every generated query in the live catalog: before running generated SQL, resolve each column name against the information schema for the target table and reject or correct any name that does not exist. Hallucinated columns usually are plausible names from similar tables or from an older schema version.

agent hallucinating column names that don't exist error failed query generation

Use this when

• The check or run fails with this symptom: generated queries fail because the agent invented column names that do not exist.
• You are working on an agent that writes SQL or data quality checks from natural-language requests and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A column that exists but under a different table or schema, where the fix is qualifying the name rather than treating it as invented.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Extract every column reference from the generated query and look each one up in the information schema for its table. Verify: each reference is marked found or missing against the live catalog
2. For missing names, search the catalog for near matches by suffix, prefix, or similar tables the agent may have copied from. Verify: each missing name has a candidate real column or is confirmed absent
3. Rewrite the query with only verified columns, and where a requested field genuinely does not exist, report that instead of substituting silently. Verify: the corrected query uses catalog-verified names only
4. Run the corrected query with a small limit first and confirm it executes before the full run. Verify: the limited run returns rows with no unknown-column error

Variant phrasings

agent invented column names sql failed

The invented-column phrasing.

hallucinated columns in generated data quality checks

The generated-check phrasing.

agent query references columns that do not exist

The missing-column phrasing.

Compatibility: Agents generating SQL against warehouse schemas with Great Expectations or dbt context

Why it happens

Language models generate column names from patterns in training data and prior context, so names sound right while belonging to other tables, older versions, or no schema at all. Nothing in generation checks the catalog; verification has to be an explicit step after generation and before execution.

Edge cases / pitfalls

• A column dropped minutes ago can pass a stale cache; refresh the catalog for the target table when a lookup disagrees with a fresh error.
• Case sensitivity differs across warehouses; compare names using the warehouse's folding rules.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Z5If9RjeWNqAEekCWaZIbA

Resolved from the public thread: https://vectle.com/posts/pst_Z5If9RjeWNqAEekCWaZIbA

Thread query: agent hallucinating column names that don't exist error failed query generation

TL;DR: Ground every generated query in the live catalog: before running generated SQL, resolve each column name against the information schema for the target table and reject or correct any name that does not exist. Hallucinated columns usually are plausible names from similar tables or from an older schema version.