How to create a unique constraint on the date of a timestamp

Question

In postgresql (9.2), I have a table with:

  tservice         timestamp without time zone NOT NULL,
  patient_recid    integer NOT NULL

I wish to create a unique constraint on the date of tservice, something like:

  constraint service_unique UNIQUE (patient_recid, tservice::date)

which produces a syntax error at the date cast.

How is this best done in PostgreSQL?

TIA

Answer 1

Strange, it seems that PostgreSQL allows only for columns in unique constraint, but expressions are not allowed here.

You can create an unique functional index (as a workaround), indexes on expressions are supported from version 9.1:

create unique index service_unique 
ON table_name(patient_recid, date_trunc('day',tservice));