// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
column "emial" does not exist
LINE 1: SELECT emial FROM users;
               ^
HINT:  Perhaps you meant to reference the column "users.email".

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 WHERE or GROUP BY expressions where it isn't visible: aliases are only usable in ORDER BY (and as a bare name in GROUP 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

  1. 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;
  2. Use single quotes for values and double quotes only for identifiers.

    SELECT * FROM users WHERE status = 'active';
  3. Quote mixed-case column names exactly as created.

    SELECT "userId", "createdAt" FROM events;
  4. 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.