postgresql - SQL: Typecast in a Unique Index -

create table foo (     dt date,     ts timestamp ); 

what's proper way create unique constraint date value of ts occurs once per dt; example:

dt         | ts 2010-01-02 | 2010-01-02 17:19:08 2010-01-02 | 2011-11-11 01:01:01 2010-01-02 | 2011-11-11 17:19:08 -- error on insert (already 2011-11-11) 

attempt:

1. invalid syntax, i'm trying achieve:

   create unique index unique_tsdate_per_dt on foo(dt,ts::date); 

2. incomplete attempt - possibly subquery?

   create unique index unique_tsdate_per_dt on foo(dt) ts::date -- ? 

i think you're looking function form of ::date cast:

create unique index unique_tsdate_per_dt on foo(dt, date(ts)); 

then you'll results this:

=> insert foo (dt, ts) values ('2010-01-02', '2010-01-02 17:19:08'); => insert foo (dt, ts) values ('2010-01-02', '2011-11-11 01:01:01'); => insert foo (dt, ts) values ('2010-01-02', '2011-11-11 17:19:08'); error:  duplicate key value violates unique constraint "unique_tsdate_per_dt" detail:  key (dt, date(ts))=(2010-01-02, 2011-11-11) exists.