duplicate column
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.
- 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)
ALTER TABLE adding an already-existing column.
ALTER TABLE employees ADD COLUMN name TEXT; -- name column already exists
expected output
ERROR: column "name" of relation "employees" already exists
Fix 1
Check if the column already exists before adding it
WHEN In migration scripts that may run multiple times.
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 When the table definition has a typo duplicating a column.
Why this works
Review the CREATE TABLE statement and remove the duplicated column name.
Class 42 — Syntax Error or Access Rule Violation
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev