42701ERRORTier 2 — Caution✅ HIGH confidence

duplicate column

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

SQLSTATE 42701 is raised when a CREATE TABLE or ALTER TABLE ADD COLUMN statement specifies the same column name more than once, or references a column that already exists in the table.

Why it happens

1CREATE TABLE defines the same column name twice
2ALTER TABLE ADD COLUMN specifies a column name that already exists in the table
3A SELECT column alias duplicates another alias in the same query (in some contexts)

How to reproduce

ALTER TABLE adding an already-existing column.

trigger — this will ERROR

ALTER TABLE employees ADD COLUMN name TEXT;
-- name column already exists

ERROR: column "name" of relation "employees" already exists

Fix 1: Check if the column already exists before adding it

In migration scripts that may run multiple times.

fix

DO $
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'employees' AND column_name = 'name'
  ) THEN
    ALTER TABLE employees ADD COLUMN name TEXT;
  END IF;
END $;

Why this works

Checking information_schema.columns guards the ADD COLUMN against running on a table that already has the column.

Fix 2: Remove the duplicate column definition from CREATE TABLE

When the table definition has a typo duplicating a column.

fix

Why this works

Review the CREATE TABLE statement and remove the duplicated column name.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 42 — Syntax Error or Access Rule Violation

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for duplicate column names. Stable across all versions.