PostgreSQL Error 42703: Column Does Not Exist

PostgreSQL SQLSTATE 42703 (undefined_column) means the query refers to a column name PostgreSQL cannot resolve. The error often includes a hint such as Perhaps you meant to reference the column .... Compare the reported name with the table definition before changing the schema. PostgreSQL lists 42703 as undefined_column in its error-code appendix.

Check the columns on the intended table

First confirm the table, schema, and column names visible to the same database role used by the failing query:

SELECT table_schema, table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
ORDER BY ordinal_position;

Replace public and orders with the schema and table from your query. The information schema only reports objects visible to the current user. If the column is absent, check whether the query targets the expected database and whether the migration that adds or renames the column has run there. Do not add a column until you confirm that the schema is meant to contain it.

Paste the missing identifier from the error and the column names returned by that query (one per line) to compare them locally:

Check spelling, table aliases, and identifier case

Compare every qualified column reference with the table alias declared in FROM or JOIN. For example, if a table is aliased as o, use o.customer_id consistently and check that customer_id exists on that table. A misspelled column or a column belonging to a different joined table can produce 42703.

PostgreSQL folds unquoted identifiers to lower case, while double-quoted identifiers preserve their exact case. If a column was created with a quoted mixed-case name, quote it with the same spelling:

CREATE TABLE public.orders ("orderID" bigint);

-- Looks for the lower-case column orderid:
SELECT orderid FROM public.orders;

-- Refers to the exact mixed-case column:
SELECT "orderID" FROM public.orders;

Prefer unquoted lower-case names for new tables and columns. See PostgreSQL’s documentation on identifiers and quoting.

Do not use a SELECT alias in WHERE or HAVING

A column label created in the SELECT list is available to ORDER BY and GROUP BY, but not to WHERE or HAVING. For example, this query cannot resolve line_total in WHERE:

SELECT quantity * unit_price AS line_total
FROM public.order_items
WHERE line_total > 100;

Repeat the expression in WHERE:

SELECT quantity * unit_price AS line_total
FROM public.order_items
WHERE quantity * unit_price > 100;

Or calculate the alias in a subquery, then filter it from the outer query:

SELECT line_total
FROM (
  SELECT quantity * unit_price AS line_total
  FROM public.order_items
) AS item_totals
WHERE line_total > 100;

PostgreSQL documents where output column names can be referenced in the SELECT command.

Distinguish a missing column from a missing table

42703 means PostgreSQL could not find the referenced column. 42702 (ambiguous_column) instead means the name matches multiple columns; see PostgreSQL Error 42702 for how to qualify it with a table alias. 42P01 (undefined_table) means PostgreSQL could not resolve the relation itself; see PostgreSQL Error 42P01. Browse the PostgreSQL error troubleshooting index for other SQLSTATE guides.