ALTER TABLE ADD CONSTRAINT fails because the constraint already exists in PostgreSQL
ERROR: constraint "my_constraint" for relation "my_table" already existsShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix