Incident brief
Column reference is ambiguous
A query referenced a bare column name that exists in more than one table in the FROM clause, so PostgreSQL could not tell which table you meant.
In 10 seconds
- What
- Column reference is ambiguous
- What triggers it
- Create two tables that share a column name (here both have id and name).
- The fix
- Qualify the column with its table name or alias (employees.id, or e.id).
- Proof
- Reproduced on PostgreSQL 16.14 → Selecting the bare column id from a USING (name) join of two tables that both have an id column was rejected with SQLSTATE 42702. Qualifying the column resolved it.
The fix
What to do right now
The immediate, application-level response to this error.
- Qualify the column with its table name or alias (employees.id, or e.id).
- If the column is a shared join key, list it in USING (...) so the join merges it into a single output column.
- Give tables short aliases and always qualify shared column names in multi-table queries.
-- qualify the column so PostgreSQL knows which table's id you mean
SELECT e.id, e.name
FROM employees e
JOIN departments d ON e.id = d.id;Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §7.2.1.1 Joined Tables
While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.Read the full section on postgresql.org →
The ambiguous, unqualified reference
USING (name) only merges the name column. Both employees and departments still expose their own id, so the bare reference id is ambiguous and PostgreSQL raises SQLSTATE 42702 instead of guessing.The session is unaffected by the parse error
42702 is a parse-time error, so nothing ran. The connection is fine and the next statement returns normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create two tables that share a column name (here both have id and name).
- 2Join them with USING (name) so only name is merged — id still exists on both sides.
- 3Select the bare column id without qualifying it with a table name or alias.
- 4PostgreSQL rejects the statement with SQLSTATE 42702 because id is present in both tables.
One client: two tables that both have id and name, joined with USING (name) so id remains on both sides.
CREATE TABLE employees (id int, name text);
CREATE TABLE departments (id int, name text);
INSERT INTO employees VALUES (1, 'Ada');
INSERT INTO departments VALUES (1, 'Engineering');SELECT id FROM employees JOIN departments USING (name);SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1ERROR: column reference "id" is ambiguous
LINE 1: SELECT id FROM employees JOIN departments USING (name);
^ session_after_error
---------------------
ok
(1 row)Qualifying the column with a table alias was tested against the same two-table join and returned the row cleanly, proving the ambiguity — not the data — was the problem.
Run the same join, but reference e.id instead of a bare id.
Without this
Above: the bare id reference raises SQLSTATE 42702.
With this, tested
Below: the qualified e.id reference returns the row.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Related errors
Glossary
Verification
- Last verified
- 2026-07-19 (isolated lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed