PostgreSQL Ambiguous Column (SQLSTATE 42702)

When a query references a column name that exists in more than one table in the FROM clause without qualifying which table it belongs to, PostgreSQL raises ERROR: column reference "x" is ambiguous with SQLSTATE 42702 and condition name AMBIGUOUS_COLUMN. The query is rejected immediately — no rows are returned and no data is modified.

What This Error Means

SQLSTATE 42702 belongs to PostgreSQL error class 42 — Syntax Error or Access Rule Violation. This class covers errors detected during query parsing and analysis, before execution begins. Because the error is raised at parse/analysis time, it has no effect on any open transaction: the transaction remains open and in its prior state (no implicit rollback occurs for this specific error, though any ongoing transaction will be marked as aborted until it is rolled back or a savepoint is used).

The parser resolves column references by looking up each unqualified name across all range table entries (tables, views, subqueries, CTEs, etc.) visible at that point in the query. If the name matches more than one source, PostgreSQL cannot pick one deterministically and refuses to proceed rather than silently choosing a column. This is intentional — silent resolution could return wrong results without any indication.

The error most commonly surfaces in JOIN queries where both sides of the join share a column name (for example, id, created_at, name, or status), but it also appears in subqueries, CTEs, set-returning functions used in FROM, and USING clauses that expose merged columns that are then referenced ambiguously elsewhere in the query.

Common Causes

1. JOIN without table-qualifying shared column names. Two or more joined tables each have a column with the same name and the SELECT list or WHERE clause references that name without a table prefix.

2. SELECT * followed by filtering on a shared column. A query like SELECT * FROM orders JOIN customers USING (id) WHERE id = 5 — even though USING merges the column, referencing it in WHERE or ORDER BY without qualification can still cause ambiguity in some query positions.

3. CTEs or subqueries that expose duplicate column names. A WITH clause or derived table that returns two columns with the same name (e.g., from its own joins) and those names are then referenced in the outer query.

4. Views or functions that return overlapping names. Joining a table against a view or set-returning function that exposes a column name already present in another part of the FROM clause.

5. Dynamic SQL or ORM-generated queries. ORMs that auto-generate JOIN queries (e.g., Rails ActiveRecord with includes, Django ORM with select_related) occasionally produce queries where shared column names are not aliased, surfacing this error when filtering on those columns.

How to Fix AMBIGUOUS_COLUMN

1. Qualify column references with the table name or alias. This is the primary fix. Add the table name (or alias) as a prefix to every ambiguous column reference:

   -- Ambiguous: raises 42702
   SELECT id, name
   FROM orders
   JOIN customers ON orders.customer_id = customers.id
   WHERE created_at > NOW() - INTERVAL '7 days';
   
   -- Fixed: qualify each ambiguous column
   SELECT orders.id, customers.name
   FROM orders
   JOIN customers ON orders.customer_id = customers.id
   WHERE orders.created_at > NOW() - INTERVAL '7 days';
   

2. Use table aliases to keep queries concise. When table names are long, define short aliases and use them consistently:

   SELECT o.id, c.name, o.created_at
   FROM orders AS o
   JOIN customers AS c ON o.customer_id = c.id
   WHERE o.status = 'pending';
   

3. Alias columns in subqueries and CTEs to avoid duplicate names. If a derived table or CTE produces two columns with the same name, rename one:

   WITH recent AS (
       SELECT o.id AS order_id, c.id AS customer_id, c.name
       FROM orders o
       JOIN customers c ON o.customer_id = c.id
   )
   SELECT order_id, customer_id, name FROM recent;
   

4. Replace SELECT * with explicit column lists in joins. SELECT * does not cause 42702 by itself, but it makes it easier to accidentally reference ambiguous names elsewhere in the query. Explicitly listing columns forces you to qualify them at the same time:

   -- Instead of SELECT *, list and qualify each needed column:
   SELECT o.id, o.total, c.email
   FROM orders o
   JOIN customers c ON o.customer_id = c.id;
   

5. Use JOIN ... USING (col) carefully. USING merges the named column into a single output column, but you must still qualify references to that column in other clauses if the column name is ambiguous elsewhere:

   -- If `id` is used in USING, reference the merged column unqualified in SELECT
   -- but be careful in WHERE and ORDER BY clauses with other same-named columns
   SELECT id, o.total, c.email
   FROM orders o
   JOIN customers c USING (id);
   

Additional Information

• SQLSTATE class 42 errors were present from the earliest PostgreSQL versions; the specific condition AMBIGUOUS_COLUMN has been part of PostgreSQL since at least version 7.4 and its behavior has not changed materially across versions.
• Related SQLSTATE codes in class 42: 42703 (UNDEFINED_COLUMN) — referenced column does not exist at all; 42P01 (UNDEFINED_TABLE) — referenced table does not exist; 42601 (SYNTAX_ERROR) — general syntax error.
• Driver/ORM behavior: Most drivers (libpq, psycopg2, psycopg3, asyncpg, JDBC) surface this as a ProgrammingError or PSQLException with the SQLSTATE 42702 included in the exception metadata. ORMs may wrap it in their own query exception types but the underlying SQLSTATE is always accessible.
• This is a parse-time error. It has no performance cost beyond the failed parse — it never touches indexes or scans any rows.
• The error message includes the column name: ERROR: column reference "created_at" is ambiguous. PostgreSQL 12+ also includes a DETAIL line in some cases identifying which tables the column was found in, which speeds up diagnosis.

Frequently Asked Questions

Why does PostgreSQL raise this error instead of just picking one of the columns? Silently picking a column would be dangerous — it could return wrong results without any warning, especially as schemas evolve and new tables with matching column names are added to a query. Refusing to proceed is the safe default and forces explicit intent.

I added a JOIN and now a query that used to work is broken. What happened? Adding a JOIN brought a new table into scope. If that table has a column with the same name as a column you were already referencing without qualification, the query that was unambiguous before is now ambiguous. Qualify the column with the original table's name or alias.

Does SELECT * trigger this error? Not directly. SELECT * expands to all columns from all tables in scope and is handled before ambiguity checking. The error is triggered when you reference a specific column name without qualification and that name exists in more than one source. However, queries using SELECT * are more likely to have unqualified column references elsewhere (in WHERE, ORDER BY, GROUP BY) that do trigger the error.

Does using JOIN ... USING prevent this error? Partially. USING (col) merges the named join column into a single output column, so referencing that specific column name in the SELECT list is no longer ambiguous. However, other columns that appear in both tables are still subject to the same ambiguity rules and must be qualified.