Incident brief
Column specified more than once
A CREATE TABLE listed the same column name twice. PostgreSQL refuses to build a table that has two columns with the same name.
What lands in your log
ERROR: column "id" specified more than once
In 10 seconds
- What triggers it
- Write a CREATE TABLE that lists the same column name (here id) two times.
- Fix
- Give every column a distinct name (here, rename the second id to user_id).
- Proof
- Reproduced on PostgreSQL 16.14 → A CREATE TABLE that listed id twice was rejected with SQLSTATE 42701 (duplicate_column). No table was created and the session stayed usable.
Fix
What to do right now
Application-level steps for this error.
- Give every column a distinct name (here, rename the second id to user_id).
- When generating DDL programmatically, de-duplicate the column list before emitting CREATE TABLE.
- Watch for a column that collides with one pulled in by LIKE or INHERITS, the merge rules can surface the same name.
DROP TABLE IF EXISTS reg_users;
CREATE TABLE reg_users (user_id int, email text);
SELECT attname FROM pg_attribute
WHERE attrelid = 'reg_users'::regclass AND attnum > 0
ORDER BY attnum;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Column specified more than once is fixed in the statement text. PostgreSQL stores no failed parse tree, so use the error POSITION against the exact SQL sent by the driver and inspect real column/function names.
Candidate columns on the referenced relations
Replace the relation array with the tables/aliases in the failing statement.
SELECT attrelid::regclass AS table_name, attnum, attname,
format_type(atttypid, atttypmod) AS data_type
FROM pg_attribute
WHERE attrelid = ANY (ARRAY[
to_regclass('<first_relation>'),
to_regclass('<second_relation>')
]::regclass[])
AND attnum > 0 AND NOT attisdropped
ORDER BY attname, table_name;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 16 Documentation, Appendix A. PostgreSQL Error Codes
All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard's conventions for “SQLSTATE” codes.Read the full section on postgresql.org →
The CREATE TABLE with a repeated column name
The column list names id twice. PostgreSQL cannot create a relation with two identically named columns, so it raises SQLSTATE 42701 and creates nothing.The session is unaffected by the parse error
Because the DDL failed at parse time outside a transaction block, the connection is untouched and the next statement runs normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Write a CREATE TABLE that lists the same column name (here id) two times.
- 2PostgreSQL rejects the DDL with SQLSTATE 42701 before the table is created.
- 3No table is created; the session is otherwise unaffected.
One client: a single CREATE TABLE statement whose column list repeats the name id.
-- A duplicate column name is a parse error, so no tables need to exist first.
SELECT 'no prior schema needed' AS setup_note;CREATE TABLE reg_users (id int, email text, id int);SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
setup_note
------------------------
no prior schema needed
(1 row)ERROR: column "id" specified more than once session_after_error
---------------------
ok
(1 row)The corrected DDL, with a distinct second column name, was executed and produced a working table and row, confirming the only defect was the repeated name.
Without this
Above: two columns named id raise SQLSTATE 42701.
With this, tested
Below: distinct column names create the table and accept a row.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-19 (isolated lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.