I need to enforce a unique constraint on the table in a text field to make sure users dont input rows containing the same address. I do not want to validate it though, old records need to be kept
I have made some check constraints with a not valid clause but not sure how to make it unique
How to make a unique check constraint not valid
I would add a new column to the table:
ALTER TABLE atable ADD is_new boolean DEFAULT FALSE NOT NULL;
ALTER TABLE atable ALTER is_new SET DEFAULT TRUE;
Then old rows will be marked, and you can
CREATE UNIQUE ON atable (col1) WHERE is_new;
which will act as a “conditional unique constraint”.
Note that adding a column with a default value will cause a (potentially slow) table rewrite before version 11.
You can't:
https://www.postgresql.org/docs/current/sql-altertable.html
"ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same constraint syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints."
Also CHECK constraints can only work on the current row so they can't enforce uniqueness across all rows. To do what you want you are looking at a ON INSERT trigger.
If I understand correctly, you can do this by using a flag. But you want to set the flag so exactly one row for each existing address has the flag set.
ALTER TABLE t ADD address_for_duplicates boolean DEFAULT true NOT NULL;
Then, for existing rows, I will assume that you have a primary key, pk:
update t
set address_for_duplicates = (seqnum = 1)
from (select t.*, row_number() over (partition by address order by pk) as seqnum
from t
) tt
where tt.pk = t.pk;
Now add a filtered unique index:
create unique index unq_t_address_some_duplicates
on t(address)
where address_for_duplicates;
This will prevent existing addresses from being duplicated (again) as well as new addresses.