↑↓ select↵ open⌫ change scopeOpen full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / SQLSTATE

42702 — ambiguous_column

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.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

8.18.28.38.49.09.19.29.39.49.59.61011121314151617181920
aliases
Unknown
class name
Syntax Error or Access Rule Violation
condition name
ambiguous_column
lang
en
sqlstate
42702

Definition PG 18

42702

At a glance {#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 {#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 {#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 {#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 {#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 {#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 {#versions}

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

Related {#related}

Sources {#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.

Compare versions

SQLSTATE evidence and recorded runtime versions

cases · ambiguous_column
{
  "assertions": [
    "SQLSTATE is 42702 with the ambiguous-reference diagnostic",
    "The session remains IDLE",
    "Qualifying the column returns the intended row"
  ],
  "case_id": "ambiguous_column",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [
    "A runner-owned disposable target is provisioned."
  ],
  "repair": "Qualify the column with its table or alias and verify the intended source.",
  "trigger": "Join two tables that both expose id and select id without qualification.",
  "versions": [
    "10",
    "18"
  ]
}
claims · identity.class-and-condition
{
  "claim_id": "identity.class-and-condition",
  "limits": "Directory identity does not identify every backend path.",
  "method": "Read fixed errcodes.txt and the locked catalogue metadata.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "42702 is the ambiguous_column condition in Class 42."
}
claims · mechanism.backend-path
{
  "claim_id": "mechanism.backend-path",
  "limits": "Other calls can use the same SQLSTATE with different context or dynamic fields.",
  "method": "Trace the resolved REL_18_6 and REL_10_23 source call records at the locked commits.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.ambiguous-column.18.6",
    "src.ambiguous-column.10.23"
  ],
  "statement": "The fixed source calls for 42702 include the mechanism and message boundary selected for this page."
}
claims · runtime.representative-case
{
  "claim_id": "runtime.representative-case",
  "limits": "This covers the selected case and targets only; it does not generalize to all drivers or intermediate releases.",
  "method": "Run the shared registry case and inspect structured diagnostics, state/recovery assertions, and cleanup.",
  "position": 2,
  "runtime": [
    "runtime.42702-syntax-latest1.latest",
    "runtime.42702-syntax-pg10.pg10"
  ],
  "sources": [
    "manifest.42702",
    "snippet-registry.42702"
  ],
  "statement": "The selected ambiguous_column case passed on isolated PostgreSQL 18.6 and 10.21 targets."
}
claims · versions.catalogue-boundary
{
  "claim_id": "versions.catalogue-boundary",
  "limits": "Definition presence is not an exact behavioral introduction.",
  "method": "Read generated facts and selected summaries.",
  "position": 3,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6",
    "src.calls.REL_18_6",
    "src.calls.REL_10_23"
  ],
  "statement": "The locked catalogue records 42702; runtime scope is limited to the selected targets."
}
messages · message.ambiguous-column
{
  "limits": "",
  "message_id": "message.ambiguous-column",
  "path": "",
  "position": 0,
  "raw": {
    "id": "message.ambiguous-column",
    "primary_template": "column reference \"%s\" is ambiguous",
    "severity_source": "explicit ERROR",
    "sources": [
      "src.ambiguous-column.18.6",
      "src.ambiguous-column.10.23"
    ],
    "sqlstate": "42702"
  },
  "severity": "explicit ERROR",
  "sources": [
    "src.ambiguous-column.18.6",
    "src.ambiguous-column.10.23"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column reference \" \" is ambiguous",
      "position": 0,
      "role": "",
      "template": "column reference \"%s\" is ambiguous"
    }
  ]
}
runtimes · runtime.42702-syntax-latest1.latest · 18.6 (Homebrew) · passed
{
  "cases": [],
  "limits": "",
  "position": 0,
  "raw": {
    "assertions": [
      {
        "expected": "42702",
        "name": "42702 SQLSTATE",
        "observed": "42702",
        "passed": true
      },
      {
        "expected": "ERROR",
        "name": "42702 ERROR severity",
        "observed": "ERROR",
        "passed": true
      },
      {
        "expected": "column reference \"id\" is ambiguous",
        "name": "42702 primary diagnostic",
        "observed": "column reference \"id\" is ambiguous",
        "passed": true
      },
      {
        "expected": "IDLE",
        "name": "ambiguous reference leaves session usable",
        "observed": "IDLE",
        "passed": true
      },
      {
        "expected": {
          "status": "IDLE",
          "value": 1
        },
        "name": "qualified reference repairs query",
        "observed": {
          "status": "IDLE",
          "value": 1
        },
        "passed": true
      }
    ],
    "case_manifest": {
      "id": "manifest.42702",
      "kind": "local_artifact",
      "path": "verify/cases/42702/cases.json",
      "sha256": "31c6d96bb62ae0e507e1a627429d53825a5ecf30942b6df342a6ec68aadec3de",
      "source_role": "case_manifest"
    },
    "cleanup": {
      "errors": [],
      "passed": true
    },
    "environment": {
      "TimeZone": "Asia/Shanghai",
      "backend_pid": 1700,
      "binary": {
        "bindir": "/opt/homebrew/Cellar/postgresql@18/18.6/bin",
        "pg_config": "/opt/homebrew/bin/pg_config",
        "pg_config_version": "PostgreSQL 18.6 (Homebrew)",
        "postgres": "/opt/homebrew/Cellar/postgresql@18/18.6/bin/postgres"
      },
      "connection_scope": "runner-owned localhost port",
      "current_user": "runner",
      "docker": {},
      "lc_messages": "C",
      "libpq_version": "18.6",
      "locale": "en_US.UTF-8",
      "log_destination": "csvlog,jsonlog",
      "log_error_verbosity": "verbose",
      "log_min_error_statement": "error",
      "log_min_messages": "error",
      "logging_collector": "on",
      "machine": "arm64",
      "max_connections": "100",
      "platform": "macOS-26.6.2-arm64-arm-64bit-Mach-O",
      "psycopg_version": "3.3.5",
      "python_version": "3.14.6 (main, Jun 10 2026, 10:03:53) [Clang 21.0.0 (clang-2100.0.123.102)]",
      "server_encoding": "UTF8",
      "server_version": "18.6 (Homebrew)",
      "server_version_num": "180006",
      "target_kind": "latest",
      "wal_level": "logical"
    },
    "id": "runtime.42702-syntax-latest1.latest",
    "observed": {
      "connection_autocommit": true,
      "diagnostic": {
        "column_name": null,
        "constraint_name": null,
        "context": null,
        "datatype_name": null,
        "exception_type": "AmbiguousColumn",
        "internal_position": null,
        "internal_query": null,
        "message_detail": null,
        "message_hint": null,
        "message_primary": "column reference \"id\" is ambiguous",
        "schema_name": null,
        "severity": "ERROR",
        "severity_nonlocalized": "ERROR",
        "source_file": "parse_relation.c",
        "source_function": "colNameToVar",
        "source_line": "964",
        "sqlstate": "42702",
        "statement_position": "8",
        "table_name": null,
        "text": "column reference \"id\" is ambiguous\nLINE 1: SELECT id FROM \"c42702_ambiguous_column\".\"left_table\", \"c427...\n               ^"
      },
      "failed_status": "IDLE",
      "repair_status": "IDLE",
      "repair_value": 1
    },
    "raw": "verify/results/42702-syntax-latest1/latest/raw.jsonl",
    "raw_sha256": "a02b48866b59e742a175f6f4149381d97944325a4ef95bb63c5f211c73978a6a",
    "run_id": "42702-syntax-latest1",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "id": "snippet-registry.42702",
      "kind": "local_artifact",
      "path": "verify/cases/42702/snippets.json",
      "sha256": "b8d1edca0e090bda71ee447d0750e13f460c603a479818170bdfea34716bdf17",
      "source_role": "snippet_registry"
    },
    "status": "passed",
    "summary": "verify/results/42702-syntax-latest1/latest/summary.json",
    "summary_sha256": "3f27e6d4e7ad01d6c4385f7b157caa93d96bf2d1f2e051794cfca7e7f0b3f0c8",
    "target": "latest"
  },
  "run_id": "42702-syntax-latest1",
  "runtime_id": "runtime.42702-syntax-latest1.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}
runtimes · runtime.42702-syntax-pg10.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed
{
  "cases": [],
  "limits": "",
  "position": 1,
  "raw": {
    "assertions": [
      {
        "expected": "42702",
        "name": "42702 SQLSTATE",
        "observed": "42702",
        "passed": true
      },
      {
        "expected": "ERROR",
        "name": "42702 ERROR severity",
        "observed": "ERROR",
        "passed": true
      },
      {
        "expected": "column reference \"id\" is ambiguous",
        "name": "42702 primary diagnostic",
        "observed": "column reference \"id\" is ambiguous",
        "passed": true
      },
      {
        "expected": "IDLE",
        "name": "ambiguous reference leaves session usable",
        "observed": "IDLE",
        "passed": true
      },
      {
        "expected": {
          "status": "IDLE",
          "value": 1
        },
        "name": "qualified reference repairs query",
        "observed": {
          "status": "IDLE",
          "value": 1
        },
        "passed": true
      }
    ],
    "case_manifest": {
      "id": "manifest.42702",
      "kind": "local_artifact",
      "path": "verify/cases/42702/cases.json",
      "sha256": "31c6d96bb62ae0e507e1a627429d53825a5ecf30942b6df342a6ec68aadec3de",
      "source_role": "case_manifest"
    },
    "cleanup": {
      "errors": [],
      "passed": true
    },
    "environment": {
      "TimeZone": "Etc/UTC",
      "backend_pid": 79,
      "binary": {},
      "connection_scope": "runner-owned localhost port",
      "current_user": "postgres",
      "docker": {
        "architecture": "arm64",
        "command": "/usr/local/bin/docker",
        "digest": "sha256:b2baf8998630663d21370da06387c950e587071bdd307ee34e661cdcc7442bcc",
        "id": "sha256:b2baf8998630663d21370da06387c950e587071bdd307ee34e661cdcc7442bcc",
        "image": "postgres:10",
        "image_created": "2022-06-23T09:11:18.374607054Z",
        "image_ref": "postgres@sha256:b2baf8998630663d21370da06387c950e587071bdd307ee34e661cdcc7442bcc",
        "log_destination": "csvlog",
        "os": "linux",
        "repo_digests": [
          "postgres@sha256:b2baf8998630663d21370da06387c950e587071bdd307ee34e661cdcc7442bcc"
        ]
      },
      "lc_messages": "en_US.utf8",
      "libpq_version": "18.6",
      "locale": "en_US.UTF-8",
      "log_destination": "csvlog",
      "log_error_verbosity": "verbose",
      "log_min_error_statement": "error",
      "log_min_messages": "error",
      "logging_collector": "on",
      "machine": "arm64",
      "max_connections": "100",
      "platform": "macOS-26.6.2-arm64-arm-64bit-Mach-O",
      "psycopg_version": "3.3.5",
      "python_version": "3.14.6 (main, Jun 10 2026, 10:03:53) [Clang 21.0.0 (clang-2100.0.123.102)]",
      "server_encoding": "UTF8",
      "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
      "server_version_num": "100021",
      "target_kind": "pg10",
      "wal_level": "logical"
    },
    "id": "runtime.42702-syntax-pg10.pg10",
    "observed": {
      "connection_autocommit": true,
      "diagnostic": {
        "column_name": null,
        "constraint_name": null,
        "context": null,
        "datatype_name": null,
        "exception_type": "AmbiguousColumn",
        "internal_position": null,
        "internal_query": null,
        "message_detail": null,
        "message_hint": null,
        "message_primary": "column reference \"id\" is ambiguous",
        "schema_name": null,
        "severity": "ERROR",
        "severity_nonlocalized": "ERROR",
        "source_file": "parse_relation.c",
        "source_function": "colNameToVar",
        "source_line": "791",
        "sqlstate": "42702",
        "statement_position": "8",
        "table_name": null,
        "text": "column reference \"id\" is ambiguous\nLINE 1: SELECT id FROM \"c42702_ambiguous_column\".\"left_table\", \"c427...\n               ^"
      },
      "failed_status": "IDLE",
      "repair_status": "IDLE",
      "repair_value": 1
    },
    "raw": "verify/results/42702-syntax-pg10/pg10/raw.jsonl",
    "raw_sha256": "365d4cc4c578a87ba6584daf52957335434f92cf584bf8711a9346d4a5199008",
    "run_id": "42702-syntax-pg10",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "id": "snippet-registry.42702",
      "kind": "local_artifact",
      "path": "verify/cases/42702/snippets.json",
      "sha256": "b8d1edca0e090bda71ee447d0750e13f460c603a479818170bdfea34716bdf17",
      "source_role": "snippet_registry"
    },
    "status": "passed",
    "summary": "verify/results/42702-syntax-pg10/pg10/summary.json",
    "summary_sha256": "7dd5ef10f868a10985e0b82ad33629f8af8ba11be2824f2e98b59da55cfe669e",
    "target": "pg10"
  },
  "run_id": "42702-syntax-pg10",
  "runtime_id": "runtime.42702-syntax-pg10.pg10",
  "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
  "status": "passed",
  "target": "pg10"
}
sources · src.errcodes.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt"
}
sources · src.calls.REL_18_6
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 1,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6",
  "tag": "",
  "url": ""
}
sources · src.calls.REL_10_23
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_10_23.jsonl",
  "position": 2,
  "sha256": "00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c",
  "source_id": "src.calls.REL_10_23",
  "tag": "",
  "url": ""
}
sources · src.ambiguous-column.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/parser/parse_relation.c",
  "position": 3,
  "sha256": "0d9c88f4a8def4c2d982c33f13208590e21ecf5e6f8cd9d7faa223dc771c9a9a",
  "source_id": "src.ambiguous-column.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_relation.c#L960-L964"
}
sources · src.ambiguous-column.10.23
{
  "commit": "02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/parser/parse_relation.c",
  "position": 4,
  "sha256": "a34f40fc93fa5df0015fe5af5ee7761ccba2d16a791cda69ae07848ed27616b5",
  "source_id": "src.ambiguous-column.10.23",
  "tag": "REL_10_23",
  "url": "https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/parser/parse_relation.c#L787-L791"
}
sources · manifest.42702
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "verify/cases/42702/cases.json",
  "position": 5,
  "sha256": "31c6d96bb62ae0e507e1a627429d53825a5ecf30942b6df342a6ec68aadec3de",
  "source_id": "manifest.42702",
  "tag": "",
  "url": ""
}
sources · snippet-registry.42702
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "verify/cases/42702/snippets.json",
  "position": 6,
  "sha256": "b8d1edca0e090bda71ee447d0750e13f460c603a479818170bdfea34716bdf17",
  "source_id": "snippet-registry.42702",
  "tag": "",
  "url": ""
}

Source references

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:a98b4dbb37246db878a9a1303cea5d3466fc55e929de197920c532e31442a64d

Text language: en · english-manuals:a98b4dbb37246db878a9a1303cea5d3466fc55e929de197920c532e31442a64d