1

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.

Alok
  • 7,734
  • 8
  • 55
  • 100

1 Answers1

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'));
Jaisus
  • 1,019
  • 5
  • 14
  • 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 – Andrey Jan 22 '23 at 21:07