PostgreSQL 42701 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42701: Duplicate Column

PostgreSQL error 42701 (duplicate_column) is raised when you attempt to define the same column name more than once within a table definition, an ALTER TABLE statement, or a query result set. The database engine enforces uniqueness of column names within any single table or result set, so it aborts the operation immediately upon detecting the conflict.

---

Top 3 Causes

1. Adding an Already-Existing Column with ALTER TABLE

Running migration scripts multiple times without idempotency guards is the most common culprit.

-- This fails if 'email' already exists
ALTER TABLE users ADD COLUMN email VARCHAR(255);
-- ERROR: 42701 - column "email" of relation "users" already exists

-- Fix: use IF NOT EXISTS (PostgreSQL 9.6+)
ALTER TABLE users ADD COLUMN IF NOT EXISTS email VARCHAR(255);

-- Fix for older versions: guard with a DO block
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name   = 'users'
          AND column_name  = 'email'
    ) THEN
        ALTER TABLE users ADD COLUMN email VARCHAR(255);
    END IF;
END;
$$;

2. Duplicate Column Name in CREATE TABLE

Manually editing large DDL scripts or copying column definitions can accidentally introduce duplicate names.

-- Broken: 'created_at' declared twice
CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    status     VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW()  -- duplicate! → 42701
);

-- Fix: remove the duplicate and use IF NOT EXISTS
CREATE TABLE IF NOT EXISTS orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    status     VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

3. Column Name Collision in CREATE TABLE AS or Joins

Using SELECT * across joined tables that share common column names (e.g., id, created_at) causes a name collision in the result set.

-- Fails because both tables have 'id' and 'created_at'
CREATE TABLE user_orders AS
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id;
-- ERROR: 42701 - column "id" specified more than once

-- Fix: explicitly alias every ambiguous column
CREATE TABLE user_orders AS
SELECT
    u.id         AS user_id,
    u.email,
    u.created_at AS user_created_at,
    o.id         AS order_id,
    o.status,
    o.created_at AS order_created_at
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Alternative: use USING to collapse shared join columns
SELECT u.id, u.email, o.status, o.total_price
FROM users u
JOIN orders o USING (id);

---

Quick Fix Checklist

Scenario	Fix
ALTER TABLE ADD COLUMN on existing column	Add IF NOT EXISTS
Duplicate in CREATE TABLE DDL	Remove the duplicate definition
SELECT * join collision	Alias conflicting columns explicitly
Legacy PostgreSQL (<9.6)	Use a DO $$ ... $$ guard block

---

Prevention Tips

1. Always write idempotent migration scripts.
Adopt tools like Flyway or Liquibase to track migration history. If you use plain SQL, apply IF NOT EXISTS to every ADD COLUMN and CREATE TABLE statement as a non-negotiable team standard.

2. Audit columns before running DDL in production.
Query information_schema.columns before executing any schema change to confirm the current state of the table.

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

Make this a required step in your deployment runbook—catching a 42701 in a pre-flight check is far less painful than rolling back a failed production migration.

---

Related Errors

• 42P07 duplicate_table – triggered when CREATE TABLE targets an already-existing table name.
• 42710 duplicate_object – raised for duplicate indexes, constraints, or sequences.
• 42712 duplicate_alias – occurs when two columns in a SELECT list share the same alias.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.