SQL - make a column unique after some timestamp?-CodePudding

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?

CodePudding user response：

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';