2

Is it possible in Postgresql 9.3 to apply a UNIQUE constraint to a table where one of the columns in the constraint is casted? I have a TIMESTAMP column that I would like to cast to a DATE and use as part of the UNIQUE constraint. However, Postgresql is throwing a syntax error.

See full example with My SQLFiddle

Dowwie
  • 1,893
  • 3
  • 18
  • 23
  • 4
    Do you need a constraint? I think constraint can't use expression, but a unique *index* can. The following works and serves more or less the same purpos: `create UNIQUE index on student_attendance (fullname, class, cast(record_time as date));` –  Jan 25 '14 at 16:53
  • This is very similar to [Postgres UNIQUE CONSTRAINT for array](http://stackoverflow.com/q/8443716/479863) but I'm not sure if it counts as a duplicate. Same solution in any case. – mu is too short Jan 25 '14 at 18:26

1 Answers1

3

A Unique Index did the trick:

create UNIQUE index on student_attendance (fullname, class, cast(record_time as date));

credit goes to *a_horse_with_no_name*

Dowwie
  • 1,893
  • 3
  • 18
  • 23