Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ALTER TABLE ADD CONSTRAINT fails because the constraint already exists in PostgreSQL

ErrorERROR: constraint "my_constraint" for relation "my_table" already exists

Short answer

PostgreSQL has no IF NOT EXISTS for ADD CONSTRAINT, so a second run of the same migration fails. Drop the constraint with DROP CONSTRAINT IF EXISTS and add it again in one ALTER TABLE statement, or look the name up in pg_constraint first and choose another name if a different rule uses it.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    List the constraints on the table with their types, and see whether the name is already used.

    SELECT conname, contype, pg_get_constraintdef(oid)
    FROM pg_constraint
    WHERE conrelid = 'public.my_table'::regclass;
  2. 2Match what you see, then jump to its fix

    1. 1The listing shows the same name with the same definition.The migration ran before and is being run againFix
    2. 2The listing shows the name, but the type or definition is not what you are adding.A different constraint on the same table already uses the nameFix

What does the error look like?

  • ERROR: constraint "my_constraint" for relation "my_table" already exists

Cause 1 of 2

The migration ran before and is being run again

1Check

The ALTER TABLE documentation shows IF NOT EXISTS for ADD COLUMN only. ADD CONSTRAINT has no such form, so a repeat run raises the duplicate error.

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'my_constraint';

2Fix

Drop and add in one statement. DROP CONSTRAINT supports IF EXISTS and issues only a notice when the name is missing. Note that a unique or primary key check runs again when you re-add it.

ALTER TABLE my_table
    DROP CONSTRAINT IF EXISTS my_constraint,
    ADD CONSTRAINT my_constraint CHECK (price >= 0);

3Verify

Run the statement twice. Neither run raises an error.

SELECT conname FROM pg_constraint WHERE conname = 'my_constraint';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 2

A different constraint on the same table already uses the name

1Check

A name can be used only once on the same table, whatever the constraint type. Decode contype to see what holds the name: c check, f foreign key, p primary key, u unique, x exclusion.

SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'public.my_table'::regclass AND conname = 'my_constraint';

2Fix

Give the new rule a different, descriptive name. Do not drop the old one unless you are sure nothing depends on it.

ALTER TABLE my_table
    ADD CONSTRAINT my_table_price_non_negative CHECK (price >= 0);

3Verify

The listing shows both constraints with their own names.

SELECT conname, contype FROM pg_constraint WHERE conrelid = 'public.my_table'::regclass;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Use a migration tool that records which scripts already ran, so a script runs once.
  • Follow one naming pattern such as table_column_rule so names rarely clash.
  • Write drop-and-add as one ALTER TABLE statement so the table is never left without the rule between steps.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.