Applying Unique Constraint Of Date On Timestamp Column In Postgresql

I have a postgresql table as CREATE TABLE IF NOT EXISTS table_name ( expiry_date DATE NOT NULL, created_at TIMESTAMP with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

Solution 1:

If you do not need a time zone for your created date : create a unique index has follows :

createunique index idx_user_review_uniq_key on  table_name (expiry_date, cast(created_at asdate));

If you need that badly to have a time zone then you need to use a little trick (https://gist.github.com/cobusc/5875282) :

createunique index idx_user_review_uniq_key on  table_name (expiry_date, date(created_at atTIME zone 'UTC'));