PostgreSQL Error 42702: Column Reference Is Ambiguous

PostgreSQL SQLSTATE 42702 (ambiguous_column) means a column reference matches more than one column in the query’s current table scope. A common message is column reference "id" is ambiguous. This often happens after a JOIN when both tables have a column with the same name. PostgreSQL lists 42702 as ambiguous_column in its error-code appendix.

Qualify the column with its table alias

Suppose both public.users and public.orders have an id column:

SELECT id, email, total
FROM public.users AS u
JOIN public.orders AS o ON o.user_id = u.id;

The unqualified id can refer to either table, so PostgreSQL rejects the query. Use the alias of the table that owns each column:

SELECT u.id, u.email, o.total
FROM public.users AS u
JOIN public.orders AS o ON o.user_id = u.id;

Qualify other shared names in every clause too, including SELECT, WHERE, ON, GROUP BY, HAVING, and ORDER BY. For example, if both tables have a status column, write o.status = 'paid' or u.status = 'active' according to which table the condition should use.

Once a table has an alias, use that alias in the rest of the query. For example, after FROM public.users AS u, refer to u.id, not users.id; the alias becomes the name of that table reference for the query. See PostgreSQL’s documentation on table aliases.

Find which tables contain the column

If you are unsure which joined table owns a name, inspect the relevant schemas:

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name IN ('users', 'orders')
  AND column_name IN ('id', 'status')
ORDER BY column_name, table_name;

Replace the schema and table names with those in your query. The information schema reports columns visible to the current database user. Then qualify each reference with the alias for the intended table. Avoid renaming or dropping columns just to silence the error; the query usually needs to state which existing value it means.

Distinguish an ambiguous column from a missing column

42702 means more than one visible column matches the reference, so qualify it. 42703 (undefined_column) means PostgreSQL cannot find that column in the current query scope; see PostgreSQL Error 42703. Browse the PostgreSQL error troubleshooting index for other SQLSTATE guides.