// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
column reference "id" is ambiguous
LINE 1: SELECT id FROM orders JOIN customers ON customers.id = orders.customer_id;
               ^

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_at or status.
  • USING and NATURAL joins 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 TABLE output columns.

How to fix it

  1. 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;
  2. 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;
    $$;
  3. Or tell PL/pgSQL to prefer the column: add #variable_conflict use_column at 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.