PostgreSQL - Add CONSTRAINT UNIQUE and do not validate exisiting data

Question

I have a small problem with adding new CONSTRAINT to my table.
I want to add a UNIQUE CONSTRAINT and do not validate existing data in my table.

I have some duplicates in existing data and I want to leave it like it is.

I wrote a query:

ALTER TABLE tbl1 
   ADD CONSTRAINT unique_const UNIQUE (fld1, fld2) NOT VALID;

But it's not working, I got an error:

UNIQUE constraints cannot be marked NOT VALID

I also tried removing all TRIGGERS:

ALTER TABLE tbl1 DISABLE TRIGGER all;
ALTER TABLE tbl1 ADD CONSTRAINT unique_const UNIQUE (fld1, fld2);
ALTER TABLE tbl1 ENABLE TRIGGER all;

But it also does not work.

Does anyone know how to add a UNIQUE CONSTRAINT without validating existing rows?

Community · Accepted Answer · 2017-04-13 12:42:39Z

2

According to the documentation of PostgreSQL 9.6, unfortunately the NOT VALID option is currently only allowed with foreign key and CHECK constraints. This StackOverflow thread here might be related and provide ideas/basic approaches for an alternative solution, although it's from 2014.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Oct 17, 2016 at 14:25

mxscho

2,1902 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

This is also true for 9.5 and 9.6

mxscho Over a year ago

Thank you very much. Edited that. :)

eby Over a year ago

Thanks, this was useful to me too! I used NOT VALID to add back a foreign key constraint after I loaded some data that contained some records that would violate this constraint.

jcaron · Accepted Answer · 2016-10-17 14:35:06Z

2

If you can specifically identify those lines which should be ignored for the constraint, you could add a partial unique index:

CREATE UNIQUE INDEX ON table (columns) WHERE some_condition;

If you don't have an existing direct condition, you may need to add a column to the table to identify those lines that should be ignored (with an appropriate default so that new lines are counted).

Not sure if there's an equivalent ALTER TABLE syntax.

answered Oct 17, 2016 at 14:35

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

1 Comment

buncis Over a year ago

is it ok if we leave the old data without intention to correct it? so we never remove the condition from our index

Collectives™ on Stack Overflow

PostgreSQL - Add CONSTRAINT UNIQUE and do not validate exisiting data

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related