Fix "column reference is ambiguous" in PostgreSQL
ERROR: column reference "id" is ambiguous
You joined tables that share a column name, such as id or name, and then used that name on its own. Postgres sees two candidates and won't pick one for you. Add the table alias in front of the column and the error goes away.
Why it happens
- Joining tables that both have an id column (almost every table does) and writing plain id.
- A self-join, where every column exists twice because it's the same table.
- ORDER BY id after SELECT *, which returns two columns named id.
How to fix it
1. Prefix the column with a table alias
Fails
SELECT id, name, amount FROM customers c JOIN orders o ON o.customer_id = c.id;
Works
SELECT o.id AS order_id, c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id;
name and amount only exist in one table, so they work bare, but prefixing every column in a join keeps the query readable and safe if a column is added later.
2. Qualify every column in a self-join
Fails
SELECT name, m.name AS manager FROM employees e JOIN employees m ON m.id = e.manager_id;
Works
SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON m.id = e.manager_id;
In a self-join both copies have exactly the same columns, so every single column needs its alias.
3. Pick the columns instead of SELECT *
Fails
SELECT * FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY id;
Works
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY o.id;
SELECT * here returns two columns called id, so ORDER BY id is ambiguous. Postgres words this one as ORDER BY "id" is ambiguous. Name the columns you need and give them distinct aliases.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Orders with customer namesINNER JOIN
- Earning more than their managerSelf-join
- Revenue by countryJOIN + GROUP BY
- Plan behind each purchaseThree-table JOIN