ERROR 42702: Column Reference Is Ambiguous in Postgres

Error Message#

ERROR: column reference "name" is ambiguous
LINE 1: SELECT name FROM orders JOIN customers ON orders.customer_id...
               ^
SQLSTATE: 42702

What Triggers This Error#

PostgreSQL raises 42702 at parse time when a column name resolves to more than one thing and it refuses to guess:

• Unqualified column in a JOIN — the name exists in more than one table in the FROM clause
• ORDER BY or GROUP BY name matching two different output columns — two aliases with the same name pointing at different expressions
• Duplicate column names inside a subquery or CTE — the outer query can't tell which one you mean
• JOIN USING with a duplicated common column — the USING column name appears more than once on one side
• PL/pgSQL variable or parameter shadowing a column — the name could be either a variable or a table column

Fix by Scenario#

Unqualified column in a JOIN#

The most common cause. The column exists in both tables, so PostgreSQL can't resolve which one you mean:

-- Bad: both orders and customers have a "name" column
SELECT name FROM orders JOIN customers ON orders.customer_id = customers.id;

-- Good: qualify with a table alias
SELECT c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

If you qualify with the wrong alias instead — a table that doesn't have the column — you get the related error 42703: column does not exist.

ORDER BY or GROUP BY name matching two output columns#

If two output columns share an alias and the aliases point at different expressions, sorting or grouping by that name is ambiguous:

-- Bad: two different columns both aliased "x"
SELECT id AS x, customer_id AS x FROM orders ORDER BY x;
-- ERROR: ORDER BY "x" is ambiguous

-- Good: give each output column a unique alias
SELECT id AS order_id, customer_id FROM orders ORDER BY order_id;

Duplicate aliases on the same expression don't error — PostgreSQL only complains when the name resolves to different values.

Duplicate column names in a subquery or CTE#

The inner query produces two columns with the same name; the outer query can't pick one:

-- Bad: subquery exposes "a" twice
SELECT a FROM (SELECT 1 AS a, 2 AS a) s;

-- Good: unique names in the subquery's SELECT list
SELECT a FROM (SELECT 1 AS a, 2 AS b) s;

This also breaks JOIN USING: if the common column name appears more than once on either side, PostgreSQL reports common column name "k" appears more than once in left table under the same SQLSTATE.

PL/pgSQL variable or parameter shadowing a column#

Inside a PL/pgSQL function, a parameter named like a column makes every unqualified reference ambiguous. The error carries a telltale detail line:

CREATE FUNCTION get_customer(name text) RETURNS int AS $$
BEGIN
  -- ERROR: column reference "name" is ambiguous
  -- DETAIL: It could refer to either a PL/pgSQL variable or a table column.
  RETURN (SELECT id FROM customers WHERE name = name);
END;
$$ LANGUAGE plpgsql;

Three fixes, in order of preference:

-- 1. Rename the parameter (p_ prefix is a common convention)
CREATE FUNCTION get_customer(p_name text) RETURNS int AS $$
BEGIN
  RETURN (SELECT id FROM customers WHERE name = p_name);
END;
$$ LANGUAGE plpgsql;

-- 2. Qualify both sides: table alias for the column, function name for the parameter
RETURN (SELECT c.id FROM customers c WHERE c.name = get_customer.name);

-- 3. Resolve the conflict per function with a pragma
CREATE FUNCTION get_customer(name text) RETURNS int AS $$
#variable_conflict use_variable
BEGIN
  RETURN (SELECT id FROM customers WHERE customers.name = name);
END;
$$ LANGUAGE plpgsql;

Prevention#

• Qualify every column with a table alias in any query that touches more than one table — including inside views and functions
• Give output columns unique aliases; never reuse an alias for two different expressions
• In PL/pgSQL, prefix parameters and variables (p_name, v_total) so they can't collide with column names
• Set plpgsql.variable_conflict = error (the default) rather than a silent resolution mode, so collisions surface in testing instead of picking the wrong value in production