ambiguous_column column reference “…” is ambiguous — 42702

Symptoms

A query joins two or more tables that each expose a column of the same name, and you referenced that column without saying which table it belongs to. PostgreSQL cannot guess your intent, so it refuses the query with SQLSTATE 42702 instead of silently picking one.

• The statement aborts during parse/analysis; nothing is read or written.
• The caret in the log points at the bare, unqualified column name.
• Very common after adding a new JOIN to a query that used to run fine.

What the server log shows

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id FROM orders o JOIN customers c ON c.id = o.customer_id;
               ^

Why PostgreSQL raises this — what the manual says

As Section 7.2.1 The FROM Clause explains:

When a query joins two or more tables that each expose a column of the same name, an unqualified reference to that column is ambiguous; the reference must be qualified with the table name or alias (for example t1.id) so PostgreSQL knows which column is meant.

During name resolution the planner builds the set of visible columns from every range-table entry in the FROM clause. If an unqualified name matches columns from more than one entry, the reference is ambiguous and analysis fails with 42702 before any plan is produced.

Common causes

• Joining tables that share a column name such as id, name, or created_at.
• Using USING vs ON inconsistently so the merged column is not coalesced.
• A subquery exposing a column that collides with an outer column.

How to fix it

1. Qualify the column with its table or alias: SELECT o.id FROM orders o JOIN customers c ….
2. Give each table a short alias and use it everywhere in the SELECT, WHERE, and ORDER BY.
3. If the join key truly is the same column, use JOIN … USING (id) so the column is merged and can be referenced unqualified.

Diagnostic query

-- Which tables in your FROM expose this column?
SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'id'
  AND table_name IN ('orders','customers');

Related & next steps

• SQLSTATE 42702 — Ambiguous Column (full reference)
• Message: column “…” does not exist
• Glossary: Join

Reference: PostgreSQL 18 Section 7.2 “Table Expressions”.