Applying unique constraint of date on TIMESTAMP column in postgresql

Question

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),
    CONSTRAINT user_review_uniq_key UNIQUE (expiry_date, created_at::date) -- my wrong attempt of using ::
)

I want to put uniue constraint on this table in such a way that expiry_date and date of created_at should be unique. Problem is created_at column is timestamp not date.

so is there any way to put unique constraint such that expire_date and created_at::date should be unique?

My attempt was to use
CONSTRAINT user_review_uniq_key UNIQUE (expiry_date, created_at::date) which is not valid.

Jaisus · Accepted Answer · 2019-09-06 13:11:21Z

4

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

create unique index idx_user_review_uniq_key on  table_name (expiry_date, cast(created_at as date));

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

create unique index idx_user_review_uniq_key on  table_name (expiry_date, date(created_at at TIME zone 'UTC'));

edited Sep 6, 2019 at 13:11

answered Apr 25, 2019 at 11:01

Jaisus

1,1176 silver badges15 bronze badges

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

1 Comment

Andrey Over a year ago

doesn't works for me when i try to create unique , just throw error on cast ERROR: syntax error at or near "cast" LINE 2: ...id_desc_date UNIQUE (pwk_prp_id, pwk_description, cast(pwk_c... postgres 9.6

Collectives™ on Stack Overflow

Applying unique constraint of date on TIMESTAMP column in postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related