Here is my use case.
I am using Postgres 13.3
I have a people
table under a draft
schema that has multiple columns.. email
and created_at. The email should have been unique but it was not. If I alter the table to be unique, there were duplicate emails inserted before and I don’t want to lose the data by removing all the duplicates. can I make the constraint to make the email unique after now or the current timestamp?
I have tried this which obviously is a syntax error
ALTER TABLE draft.people ADD UNIQUE (email) WHERE created_at > timestamp()
I also tried to add a check constraint but it throws a syntax error..should I write a function that is triggered before insert?
Advertisement
Answer
This can only be done using a unique index.
However, you can not use current_timestamp
or now()
in the WHERE condition. You need to put in a constant value:
create unique index on draft.people (email) WHERE created_at > timestamp '2022-08-12 22:00:00';