column reference is ambiguous

What it means

The name you wrote matches more than one thing the query can see, so PostgreSQL doesn’t know which you mean. In a join of customers and invoices, both have id and created_at; a bare id could be either. The caret points at the first ambiguous use:

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id, name, total FROM customers c JOIN invoices i ON i...
               ^

Columns whose names appear in only one of the tables (name, total) are fine unqualified. The problem is only the shared ones, and it can appear anywhere: the select list, WHERE, GROUP BY, ORDER BY.

Common causes

1. A join where both tables have the column: id, created_at, name, status, tenant_id. It often appears when a join is added to a query that used to read one table.
2. ORDER BY a name that two output columns share: SELECT c.id, i.id … ORDER BY id fails with ORDER BY "id" is ambiguous.
3. INSERT … ON CONFLICT DO UPDATE: in SET qty = qty + EXCLUDED.qty, the bare qty could be the existing row or the proposed one.
4. UPDATE … FROM where the joined table shares a column used in WHERE.
5. PL/pgSQL functions whose parameters or RETURNS TABLE columns have the same names as table columns. RETURNS TABLE (id int, …) declares a variable id, which clashes with every table’s id.

How to fix it

Qualify with the table alias

SELECT c.id, c.name, i.total
FROM customers c
JOIN invoices i ON i.customer_id = c.id
WHERE i.created_at > '2026-06-01';

Qualifying every column in a join, even the unambiguous ones, keeps the query working when someone later adds a same-named column to the other table.

Name output columns apart

SELECT c.id AS customer_id, i.id AS invoice_id
FROM customers c JOIN invoices i ON i.customer_id = c.id
ORDER BY customer_id;

Join with USING when the columns are the same thing

When the join columns have the same name, USING merges them into one, and the bare name is no longer ambiguous:

SELECT customer_id, body, flag
FROM customer_notes
JOIN customer_flags USING (customer_id);

In ON CONFLICT, name the table

The existing row goes by the table’s name (or its alias), the proposed one by EXCLUDED:

INSERT INTO stock VALUES ('A', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

The column on the left of = in SET is never qualified.

In PL/pgSQL, qualify columns or rename variables

Give table columns an alias and use it everywhere in the function’s queries:

CREATE OR REPLACE FUNCTION invoices_for(customer_id int)
RETURNS TABLE (id int, total numeric) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT i.id, i.total FROM invoices i WHERE i.customer_id = invoices_for.customer_id;
END $$;

A parameter can always be written as function_name.parameter. Alternatively, prefix parameters (p_customer_id), or put #variable_conflict use_column at the top of the function body so that a clash means the column. The default is to raise this error, which is the safe choice.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE customers (id int PRIMARY KEY, name text, created_at date);
CREATE TABLE invoices (id int PRIMARY KEY, customer_id int REFERENCES customers, total numeric, created_at date);

SELECT id, name, total FROM customers c JOIN invoices i ON i.customer_id = c.id;
SELECT c.id, i.id FROM customers c JOIN invoices i ON i.customer_id = c.id ORDER BY id;

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id, name, total FROM customers c JOIN invoices i ON i...
               ^

ERROR:  ORDER BY "id" is ambiguous
LINE 1: ...omers c JOIN invoices i ON i.customer_id = c.id ORDER BY id;
                                                                    ^

An upsert with a bare column on the right of SET:

CREATE TABLE stock (sku text PRIMARY KEY, qty int);
INSERT INTO stock VALUES ('A', 1);
INSERT INTO stock VALUES ('A', 5) ON CONFLICT (sku) DO UPDATE SET qty = qty + EXCLUDED.qty;

ERROR:  column reference "qty" is ambiguous
LINE 1: ...ES ('A', 5) ON CONFLICT (sku) DO UPDATE SET qty = qty + EXCL...
                                                             ^

With stock.qty + EXCLUDED.qty it returns A | 6. A PL/pgSQL function with RETURNS TABLE (id int, total numeric) and SELECT id, total FROM invoices inside fails when called:

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id, total FROM invoices WHERE invoices.customer_id = ...
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT id, total FROM invoices WHERE invoices.customer_id = invoices_for.customer_id
CONTEXT:  PL/pgSQL function invoices_for(integer) line 3 at RETURN QUERY

The qualified version above, and a version with #variable_conflict use_column, both return the invoice. PostgreSQL 14, 15, 16 and 17 print the same messages.

In Inlet

The query editor completes column names from the live schema, and Inlet shows the error at the position the server reports, which here is the first ambiguous name. With your own Anthropic API key, Ask Claude (⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.