42702

At a glance

SQLSTATE 42702 is ambiguous_column in Class 42. 42702 is ambiguous_column. The selected join exposes id from two real tables and an unqualified SELECT id reports column reference "%s" is ambiguous.

Meaning

42702 is ambiguous_column. In colNameToVar, PostgreSQL scans the visible namespace for an unqualified name. Once two visible range-table entries produce a match in the same lookup, the parser raises ERROR column reference "%s" is ambiguous; the selected join exposes id from both real tables. The branch is about name resolution, before execution or row values can disambiguate it.

Diagnosis

Inspect aliases, CTEs, join inputs, lateral visibility, and every relation contributing the name in the current query scope. An unqualified name can be ambiguous even when the two columns contain the same value; PostgreSQL must resolve the source before executing the query. This is parse-time name resolution, so the selected autocommit session remains IDLE; it is distinct from 42703 where no matching column exists. An explicit transaction still follows the usual statement-ERROR transaction-state rule.

Response

Qualify the intended column with a stable table alias (or remove the unintended relation from the scope), then verify the returned row. Do not solve the ambiguity by relying on join order or equal values. If generated SQL introduces aliases or CTEs, make the qualification part of the query-builder contract and test the selected source column.

Messages

The selected parse_relation.c branch emits an explicit ERROR with primary column reference "%s" is ambiguous; %s is the unresolved column name and parser position is contextual. A client exception without the server SQLSTATE and primary is not sufficient to claim 42702.

Representative case

The page uses the same statements as the runner registry. Generated names such as syntax_schema and syntax_role are replaced by disposable runner values when executed.

CREATE TABLE syntax_schema.left_table (id integer);
CREATE TABLE syntax_schema.right_table (id integer);
INSERT INTO syntax_schema.left_table VALUES (1);
INSERT INTO syntax_schema.right_table VALUES (1);
SELECT id FROM syntax_schema.left_table, syntax_schema.right_table;
SELECT syntax_schema.left_table.id FROM syntax_schema.left_table, syntax_schema.right_table;

The selected 18.6 run reports the structured diagnostic and passes the repair assertions; the 10.21 run passes the same case-specific checks. The downloadable case and evidence projections are 42702 case JSON and authored evidence. The runner manifest is verify/cases/42702/cases.json, and the page SQL is checked against its shared registry.

Versions

The selected natural runtime scope is PostgreSQL 18.6 and 10.21; it does not infer behavior for every intermediate release.

Related

• 42703 — undefined_column
• 42601 — syntax_error

Sources

• src.errcodes.18.6 — fixed errcodes.txt definition at commit 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).
• src.ambiguous-column.18.6 — src/backend/parser/parse_relation.c at REL_18_6 commit 724edf9bde9d356724ad384a2e196edc3c9f80f7; fixed blob SHA-256 0d9c88f4a8def4c2d982c33f13208590e21ecf5e6f8cd9d7faa223dc771c9a9a (source).
• src.ambiguous-column.10.23 — src/backend/parser/parse_relation.c at REL_10_23 commit 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; fixed blob SHA-256 a34f40fc93fa5df0015fe5af5ee7761ccba2d16a791cda69ae07848ed27616b5 (source).
• src.calls.REL_18_6 / src.calls.REL_10_23 — fixed call scans, SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf / 00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c.

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

• src/backend/utils/errcodes.txt · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• raw/calls/REL_18_6.jsonl · ·
• raw/calls/REL_10_23.jsonl · ·
• src/backend/parser/parse_relation.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_relation.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• verify/cases/42702/cases.json · ·
• verify/cases/42702/snippets.json · ·

Message templates

column reference "%s" is ambiguous

Reproduction & repair cases

Recorded runtime evidence

{
  "diagnostic": {
    "text": "column reference \"id\" is ambiguous\nLINE 1: SELECT id FROM \"c42702_ambiguous_column\".\"left_table\", \"c427...\n               ^",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "42702",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "parse_relation.c",
    "source_line": "964",
    "message_hint": null,
    "datatype_name": null,
    "exception_type": "AmbiguousColumn",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "column reference \"id\" is ambiguous",
    "source_function": "colNameToVar",
    "internal_position": null,
    "statement_position": "8",
    "severity_nonlocalized": "ERROR"
  },
  "repair_value": 1,
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}

{
  "diagnostic": {
    "text": "column reference \"id\" is ambiguous\nLINE 1: SELECT id FROM \"c42702_ambiguous_column\".\"left_table\", \"c427...\n               ^",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "42702",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "parse_relation.c",
    "source_line": "791",
    "message_hint": null,
    "datatype_name": null,
    "exception_type": "AmbiguousColumn",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "column reference \"id\" is ambiguous",
    "source_function": "colNameToVar",
    "internal_position": null,
    "statement_position": "8",
    "severity_nonlocalized": "ERROR"
  },
  "repair_value": 1,
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}