# ERROR 42702: Column Reference Is Ambiguous in Postgres

Source: https://www.bytebase.com/reference/postgres/error/42702-ambiguous-column-postgres/

---

## Error Message

```sql
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:

```sql
-- 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](/reference/postgres/error/42703-undefined-column-postgres/).

### 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:

```sql
-- 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:

```sql
-- 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:

```sql
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:

```sql
-- 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

> **Note:** Bytebase's [SQL Review](https://docs.bytebase.com/sql-review/review-rules/) can enforce alias qualification and naming conventions during change review, before ambiguous references reach production. See also [ERROR 42703: Column Does Not Exist](/reference/postgres/error/42703-undefined-column-postgres/) for the related column-not-found error.