PostgreSQL 42703: column does not exist
A column name in the query doesn't exist in the tables it can see. Often a typo, a mixed-case name, or double quotes used for a string value.
- SQLSTATE
42703- Name
undefined_column
What it means
PostgreSQL couldn't match a column reference to any column of the tables in the FROM clause. When a similar name exists, recent versions add a HINT suggesting it.
Two PostgreSQL rules cause most of these errors. Unquoted identifiers fold to lower case, so a column created as "createdAt" must be written with the quotes. And double quotes always mean an identifier, never a string, so WHERE status = "active" looks for a column named active.
Common causes
- A typo, or a column that was renamed or not added yet.
- A column created with quoted mixed-case letters (
"userId") referenced without quotes. - A string value written in double quotes:
WHERE name = "Ada"instead of'Ada'. - Referencing a select-list alias in
WHEREorGROUP BYexpressions where it isn't visible: aliases are only usable inORDER BY(and as a bare name inGROUP BY). - Using the column of a table alias that isn't the right table, or a column from a subquery that wasn't selected in it.
How to fix it
-
List the table's real column names.
SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'users' ORDER BY ordinal_position;
-
Use single quotes for values and double quotes only for identifiers.
SELECT * FROM users WHERE status = 'active';
-
Quote mixed-case column names exactly as created.
SELECT "userId", "createdAt" FROM events;
-
Repeat the expression, or wrap the query, instead of using an alias in
WHERE.SELECT * FROM ( SELECT id, price * qty AS total FROM order_lines ) t WHERE total > 100;
Reproduce it safely
CREATE TEMP TABLE users (id int, email text); SELECT emial FROM users; -- ERROR: column "emial" does not exist
Questions
Why does WHERE name = "Ada" say column Ada does not exist?
In PostgreSQL double quotes mark an identifier such as a column or table name. String values take single quotes: WHERE name = 'Ada'.
Related errors
42P01relation does not exist42702column reference is ambiguous42803column must appear in the GROUP BY clause207SQL Server: Invalid column name1054MySQL: Unknown column in field listORA-00904Oracle: invalid identifier
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42883function does not exist42P02there is no parameter
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.