Incident Response

PostgreSQL "column does not exist": 8 Causes and How to Fix Them

Most of the time the column is there, and PostgreSQL is reading your query differently: double quotes around a string, a capital letter, an alias in WHERE, a table name in UPDATE SET. The rest is deploy order, and that one hurts in production.

· 13 min read · AG Data Team

"column does not exist" means PostgreSQL could not resolve a name in your query to a column of any table in scope. The error code is 42703, undefined_column (error codes). Unlike a missing table, a missing column is very often a reading problem: the column is there, but your SQL asked for something else.

Look at the full message first. PostgreSQL reports either column "x" does not exist or column t.x does not exist for a qualified reference, and when it finds a near match it adds a hint such as Perhaps you meant to reference the column "t.x". or Perhaps you meant to reference the table alias "t". (parse_relation.c). That hint solves half the cases on its own.

1. Double quotes around a string value

SELECT * FROM orders WHERE status = "paid";
-- ERROR:  column "paid" does not exist

SELECT * FROM orders WHERE status = 'paid';   -- string constant: single quotes

This is the classic for people coming from MySQL. In PostgreSQL, string constants use single quotes; double quotes form a quoted identifier, which is always a name, never a value (lexical structure). So "paid" is a column named paid, and the error message shows your value as the missing column. The same happens when a JSON string or a parameter is pasted into SQL by hand.

2. A capital letter in the column name

CREATE TABLE users ("userId" bigint, "createdAt" timestamptz);  -- typical ORM output

SELECT userId FROM users;
-- ERROR:  column "userid" does not exist
-- HINT:  Perhaps you meant to reference the column "users.userId".

SELECT "userId" FROM users;   -- works

Unquoted names are folded to lower case; quoted names are case-sensitive, so FOO, foo and "foo" are the same, while "Foo" is different (lexical structure). ORMs and migration tools that quote every identifier create "userId", and every hand-written query must then quote it the same way. The hint usually gives it away. In our experience, the lasting fix is to choose lower-case snake_case names in the ORM configuration before the schema grows, because renaming later touches every query.

3. You are looking at a different table

-- which columns does this table really have, in which schema?
SELECT table_schema, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY table_schema, ordinal_position;

-- in psql
\d users

If information_schema.columns shows the column in app.users but your session resolves users to public.users, you are querying another table with the same name. The causes are the same as for a missing table: search_path, the wrong database, a pooler that changes session settings. They are covered in PostgreSQL relation does not exist. The same applies to a join where the column belongs to a different alias than the one you wrote.

4. An output alias used in WHERE or HAVING

-- fails: output alias is not visible in WHERE
SELECT price * qty AS total FROM order_lines WHERE total > 100;
-- ERROR:  column "total" does not exist

-- works: repeat the expression, or wrap in a subquery
SELECT price * qty AS total FROM order_lines WHERE price * qty > 100;
SELECT * FROM (SELECT price * qty AS total FROM order_lines) s WHERE total > 100;

-- ORDER BY and GROUP BY may use the alias
SELECT price * qty AS total FROM order_lines ORDER BY total DESC;

The documentation is explicit: an output column name can be used in ORDER BY and GROUP BY, but not in WHERE or HAVING; there you must write out the expression (SELECT). WHERE is evaluated before the select list exists, so the alias is simply not known yet.

5. A table name in UPDATE ... SET

UPDATE orders SET orders.status = 'paid' WHERE id = 1;   -- invalid
UPDATE orders SET status = 'paid' WHERE id = 1;          -- correct

-- qualification is fine (and often needed) on the right-hand side and in WHERE
UPDATE orders o SET status = s.status
FROM staging s
WHERE s.id = o.id;

Per the UPDATE documentation, you must not include the table's name in the target column: UPDATE table_name SET table_name.col = 1 is invalid (UPDATE). PostgreSQL reads orders on the left side as a column name, and the error names orders as the missing column. Code ported from other databases, or generated by a tool, often trips on this.

6. PL/pgSQL functions that fail weeks later

A PL/pgSQL function with a typo in a column name, or one that references a column you dropped, can be created without any error. The documentation explains why: SQL commands inside a function are parsed and analyzed only when execution first reaches them, so errors in a specific command are not detected until that part of the function runs (PL/pgSQL internals). A rarely used branch can fail long after the migration that broke it. In our experience, calling every branch of critical functions in a test after each migration, or using a linter such as plpgsql_check, catches these before production does.

Views behave differently: dropping a column that a view depends on is refused unless you add CASCADE, which drops the view too (ALTER TABLE). That error is your friend; do not reach for CASCADE without reading what it will drop.

7. Code and schema out of step

The production version of this error is usually a deploy problem:

The safe pattern is expand and contract: add the new column, deploy code that writes both, backfill, switch reads, and drop the old column only when no code uses it. Details in zero-downtime PostgreSQL migrations.

Never rename a column that running code uses. Add a new one, migrate in steps and drop the old one in a separate release. A rename that takes milliseconds in the database can take every application server down with it.

8. Logical replication

Logical replication does not replicate schema or DDL. When the publisher's schema changes and replicated data starts arriving that does not fit the subscriber's table, replication errors until the schema is updated; the documentation suggests applying additive schema changes to the subscriber first (logical replication restrictions). Add new columns on subscribers before the publisher, and drop them on the publisher first.

What changes on big tables and many databases

On a test database with a thousand rows, adding a column and fixing a query takes a minute. In production:

Key takeaways

PostgreSQL Errors SQL Migrations
Work with us

Migrations breaking on missing columns?

We review schema changes before they hit production, set up expand-and-contract migrations, and fix deploys where code and schema get out of step.

Get in touch More from the blog