42701

At a glance

SQLSTATE 42701 is duplicate_column in Class 42. 42701 is duplicate_column. The selected CREATE TABLE path lists id twice and reports column "%s" specified more than once before a repaired table with distinct columns is created.

Meaning

42701 is duplicate_column. While processing a table definition, PostgreSQL compares the local ColumnDef names before merging inherited attributes. Listing the same name twice in one definition therefore reports ERROR column "%s" specified more than once before any repaired table is created. This local-name check is separate from inherited default, type, or collation conflicts, which can use other SQLSTATEs.

Diagnosis

Inspect the generated column list after expansion, including quoted identifiers and migration fragments that are concatenated into one CREATE TABLE. PostgreSQL compares the identifier spelling after normal name processing, so an accidental repeated unquoted name is still one column name. The selected DDL ERROR leaves the autocommit session IDLE; inside an explicit BEGIN, the same error leaves the transaction INERROR until ROLLBACK or a suitable savepoint rollback before retrying. Distinguish this local duplicate-name branch from 42611’s inherited-default conflict and from type/collation errors while merging inherited columns.

Response

Remove the repeated definition or give it an intentional distinct name, rerun the complete DDL, and inspect the resulting catalog. If the duplicate came from a migration that already ran, compare the existing table definition before choosing ALTER TABLE versus a new CREATE TABLE; do not assume a failed CREATE TABLE partially created the relation. The selected repair creates id and payload and confirms two columns.

Messages

The selected tablecmds.c branch emits an explicit ERROR with primary column "%s" specified more than once; %s is the repeated column name. This message is about duplicate local column definitions, while neighboring source branches can report different SQLSTATEs for inherited type/collation conflicts. A client exception without the server SQLSTATE and primary is not sufficient to claim 42701.

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.duplicate_table (id integer, id text);
CREATE TABLE syntax_schema.repaired_table (id integer, payload text);
SELECT count(*) FROM information_schema.columns WHERE table_schema = 'syntax_schema_name' AND table_name = 'repaired_table_name';

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 42701 case JSON and authored evidence. The runner manifest is verify/cases/42701/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

• 42611 — invalid_column_definition
• 42703 — undefined_column

Sources

• src.errcodes.18.6 — fixed errcodes.txt definition at commit 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).
• src.duplicate-column.18.6 — src/backend/commands/tablecmds.c at REL_18_6 commit 724edf9bde9d356724ad384a2e196edc3c9f80f7; fixed blob SHA-256 422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9 (source).
• src.duplicate-column.10.23 — src/backend/commands/tablecmds.c at REL_10_23 commit 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; fixed blob SHA-256 6de441c88496c6cf57a836898388085ec08bf69afd70d07acdafd6089b9b5f8c (source).
• src.calls.REL_18_6 / src.calls.REL_10_23 — fixed call scans, SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf / 00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c.