PostgreSQL Error 42702

SQLSTATE

42702

Condition name

ambiguous_column

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Syntax & Names

Last reviewed

24 October 2026

What it means

SQLSTATE 42702 means an unqualified column name matches columns in more than one table of the query, or, inside a PL/pgSQL function, matches both a variable and a table column, so PostgreSQL will not guess which one you meant.

When a query joins tables that share a column name, such as dept_id or name in both emp and dept, an unqualified reference to it could mean either table. PostgreSQL refuses to choose and raises 42702 (ambiguous_column). The message names the column but not the tables; check which tables in FROM have it. The fix is to qualify the column with its table or alias (e.dept_id). A join written with USING (dept_id) merges the two columns into one, so unqualified dept_id is then allowed; columns not listed in USING stay ambiguous. ORDER BY has its own form, "ORDER BY "name" is ambiguous", when two output columns have the same name.

In PL/pgSQL functions the error has a DETAIL line: It could refer to either a PL/pgSQL variable or a table column. Parameters, declared variables and the output columns of RETURNS TABLE are all variables, so RETURNS TABLE (emp_id int, ...) followed by SELECT emp_id FROM emp is ambiguous. CONTEXT names the function and line. LANGUAGE sql functions behave differently: there the column silently wins over a parameter of the same name, which raises no error but can return wrong results.

Common causes

• A join of two tables that share a column name (id, name, dept_id, created_at), with the column written unqualified in SELECT, WHERE, ON, GROUP BY or ORDER BY.
• A join condition such as ON dept_id = dept_id, which is ambiguous on both sides.
• A query that used to read one table and then gained a JOIN, so names that were unique are not any more.
• A PL/pgSQL parameter or variable named like a column (dept_id), especially the output columns of RETURNS TABLE, used inside a query.
• Two output columns with the same name (e.name and d.name) and an ORDER BY name.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name IN ('emp', 'dept')   -- the tables in your FROM clause
  AND column_name = 'dept_id'          -- the name from the message
ORDER BY table_name;

SELECT proname,
       pg_get_function_arguments(oid) AS arguments,
       pg_get_function_result(oid)    AS result
FROM pg_proc
WHERE proname = 'get_emp';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT e.emp_id, e.name AS emp_name, d.name AS dept_name
FROM emp AS e
JOIN dept AS d ON d.dept_id = e.dept_id
ORDER BY emp_name;

SELECT dept_id, e.name
FROM emp AS e
JOIN dept AS d USING (dept_id);

CREATE OR REPLACE FUNCTION get_emp(p int)
RETURNS TABLE (emp_id int, name text)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT e.emp_id, e.name
    FROM emp AS e
    WHERE e.dept_id = p;
END
$$;

Examples

Selecting a column that both joined tables have

CREATE TABLE emp (emp_id int, name text, dept_id int);
CREATE TABLE dept (dept_id int, name text);

SELECT dept_id, emp_id
FROM emp
JOIN dept ON emp.dept_id = dept.dept_id;

ERROR:  column reference "dept_id" is ambiguous

An OUT column of RETURNS TABLE clashing with a table column

CREATE FUNCTION succeeds: PL/pgSQL checks the query only when the function first runs it. The fix is to write SELECT e.emp_id, e.name FROM emp AS e WHERE e.dept_id = p.

CREATE TABLE emp (emp_id int, name text, dept_id int);

CREATE FUNCTION get_emp(p int)
RETURNS TABLE (emp_id int, name text)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY SELECT emp_id, name FROM emp WHERE dept_id = p;
END
$$;

SELECT * FROM get_emp(1);

ERROR:  column reference "emp_id" is ambiguous
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
CONTEXT:  PL/pgSQL function get_emp(integer) line 3 at RETURN QUERY

ORDER BY a name two output columns share

Both output columns are called name. Give them different aliases (emp_name, dept_name) or order by e.name.

CREATE TABLE emp (emp_id int, name text, dept_id int);
CREATE TABLE dept (dept_id int, name text);

SELECT e.name, d.name
FROM emp AS e
JOIN dept AS d USING (dept_id)
ORDER BY name;

ERROR:  ORDER BY "name" is ambiguous

How to prevent it

In any query with more than one table, alias every table and qualify every column, including in ON, WHERE, GROUP BY and ORDER BY. Give output columns distinct names when two tables share a column such as name.

In PL/pgSQL, use a naming convention for parameters and variables (p_ and v_ prefixes) and always alias tables in function queries. Remember that LANGUAGE sql functions give no error for the same clash (the column wins), so the convention matters there too.

Common questions

Learn more on CodeWithSQL

• SQL joins guide
• INNER, LEFT, RIGHT and FULL JOIN

Official documentation

• PostgreSQL error codes (Appendix A)
• Table expressions: joins, USING and aliases
• PL/pgSQL variable substitution
• Query language (SQL) functions: argument names

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.