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.

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