PostgreSQL Duplicate Column (SQLSTATE 42701)

When PostgreSQL encounters a column name used more than once in the same context, it raises:

ERROR:  column "column_name" specified more than once
SQLSTATE: 42701

The condition name is duplicate_column. This error is a DDL or query-parse-time failure — PostgreSQL rejects the statement before any data is read or written.

What This Error Means

SQLSTATE 42701 belongs to PostgreSQL error class 42, "Syntax Error or Access Rule Violation." This class covers errors that PostgreSQL detects during statement parsing and semantic analysis, before execution begins. The connection remains open and the transaction (if any) is still active after this error — only the offending statement is aborted.

The duplicate_column error means PostgreSQL found the same column name appearing more than once in a position where column names must be unique. Column names within a single table must be unique; PostgreSQL enforces this both when you create the table and when a query produces a result set that would have duplicate output column names in contexts that require uniqueness.

The error is raised deterministically at parse or planning time. It does not depend on the data in your tables and will always fail the same way until the statement is corrected.

Common Causes

1. Listing the same column twice in CREATE TABLE — The most straightforward trigger: you define a column, then define it again (perhaps after adding a new column and forgetting an existing one with the same name).

2. ADD COLUMN on a column that already exists — Running ALTER TABLE t ADD COLUMN col text when col is already present in the table raises this error.

3. SELECT with an explicit column list that repeats a name in a context requiring uniqueness — For example, creating a view or using INSERT INTO ... SELECT where the target or source has a duplicated column name.

4. CREATE TABLE AS SELECT or CREATE VIEW AS SELECT with ambiguous column names — When the driving SELECT produces two output columns with the same name (e.g., from a join where both sides have a column called id and neither is aliased), PostgreSQL raises 42701.

5. Generated migrations or ORM auto-migrations — Code-generation tools and schema-migration libraries can produce duplicate ADD COLUMN statements when migration state is out of sync with the actual schema.

How to Fix duplicate_column

1. Remove or rename the duplicate column definition in CREATE TABLE.

   -- Broken
   CREATE TABLE orders (
       id          bigint PRIMARY KEY,
       customer_id bigint,
       customer_id text   -- duplicate!
   );
   
   -- Fixed
   CREATE TABLE orders (
       id          bigint PRIMARY KEY,
       customer_id bigint
   );
   

2. Guard ADD COLUMN with IF NOT EXISTS (PostgreSQL 9.6+).

   ALTER TABLE orders ADD COLUMN IF NOT EXISTS customer_id bigint;
   

   This is the safest form for migration scripts — it silently skips the operation when the column already exists rather than raising an error.

3. Alias duplicate columns in CREATE TABLE AS or CREATE VIEW.

   -- Broken (both tables have a column named "id")
   CREATE VIEW order_details AS
   SELECT o.id, c.id, c.name
   FROM orders o
   JOIN customers c ON c.id = o.customer_id;
   
   -- Fixed
   CREATE VIEW order_details AS
   SELECT o.id AS order_id, c.id AS customer_id, c.name
   FROM orders o
   JOIN customers c ON c.id = o.customer_id;
   

4. Check the current table schema before running migrations.

   SELECT column_name, data_type
   FROM information_schema.columns
   WHERE table_schema = 'public'
     AND table_name   = 'orders'
   ORDER BY ordinal_position;
   

   Compare this against what your migration script intends to add before executing it.

5. Regenerate or reconcile ORM migration state. If an ORM (SQLAlchemy, Django ORM, ActiveRecord, etc.) produced the failing migration, check whether the column was added manually or by a previous migration that was not recorded in the migration history table. Resolve the discrepancy before re-running.

Additional Information

• IF NOT EXISTS for ADD COLUMN was introduced in PostgreSQL 9.6. On older versions you must check information_schema.columns yourself before adding a column.
• Related error in the same class 42: 42703 undefined_column (column referenced does not exist) — essentially the opposite problem.
• 42P16 invalid_table_definition covers broader table-definition violations; 42701 is the specific sub-case for column name collisions.
• Most PostgreSQL drivers (libpq, psycopg, asyncpg, JDBC) surface this as a ProgrammingError or SQLException with getSQLState() returning "42701". The full error message is available in the exception detail.
• ORMs that use auto-migration (e.g., SQLAlchemy create_all, Hibernate update) may silently skip columns they detect already exist, but explicit migration tools (Alembic, Flyway, Liquibase) will raise this error if the migration script is replayed without proper guards.

Frequently Asked Questions

Why does my migration script fail with 42701 on some environments but not others?

The column already exists in the environments where it fails and does not exist in the environments where it succeeds. This usually means a previous migration was applied manually, or migration tracking is out of sync. Use ADD COLUMN IF NOT EXISTS to make the migration idempotent, and reconcile the migration history table.

Can 42701 occur during a SELECT query, not just DDL?

Yes, but only in contexts where the query result must have unique column names: CREATE TABLE AS SELECT, CREATE VIEW AS SELECT, CREATE MATERIALIZED VIEW AS SELECT, and similar. A plain SELECT returning duplicate column names will succeed and return both columns; the error only fires when PostgreSQL needs to register those names in a schema object.

How do I find which column is duplicated when the error message is not obvious?

The error message always names the offending column — column "foo" specified more than once. Search the failing DDL statement or migration file for every occurrence of that column name. In long auto-generated migration files, a simple grep or text search for the column name is the fastest approach.

Does duplicate_column roll back my entire transaction?

Only the failing statement is aborted, not the whole transaction. However, in PostgreSQL, once any statement in a transaction raises an error the transaction enters an error state and subsequent statements will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue ROLLBACK or ROLLBACK TO SAVEPOINT. Use savepoints around DDL statements in migration scripts if you need partial rollback capability.