PostgreSQL 42702: column reference is ambiguous
A column name exists in more than one table of the query (or matches a PL/pgSQL variable), so PostgreSQL can't tell which one you mean.
- SQLSTATE
42702- Name
ambiguous_column
What it means
When two tables in the FROM clause both have a column called id, an unqualified id could refer to either, so PostgreSQL refuses the query with 42702. Qualifying the column with its table name or alias removes the ambiguity.
In PL/pgSQL functions the same error appears when a column name matches a variable or parameter name, for example a function parameter id used in a query on a table that also has id.
Common causes
- Joined tables share a column name, often
id,name,created_atorstatus. USINGandNATURALjoins merge only the join columns; other shared names stay ambiguous.- In PL/pgSQL, a parameter or variable has the same name as a column, including
RETURNS TABLEoutput columns.
How to fix it
-
Qualify the column with its table or alias.
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
-
In PL/pgSQL, give parameters a prefix, or qualify them with the function name.
CREATE FUNCTION order_total(p_id int) RETURNS numeric LANGUAGE plpgsql AS $$ BEGIN RETURN (SELECT total FROM orders WHERE orders.id = p_id); END; $$; -
Or tell PL/pgSQL to prefer the column: add
#variable_conflict use_columnat the top of the function body.
Reproduce it safely
SELECT id FROM (VALUES (1)) a(id), (VALUES (2)) b(id); -- ERROR: column reference "id" is ambiguous
Related errors
42703column does not exist42803column must appear in the GROUP BY clause209SQL Server: Ambiguous column name1052MySQL: Column is ambiguousORA-00918Oracle: column ambiguously defined
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 exist42P01relation does not exist
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.