PostgreSQL multiple column UNIQUE on formatted value

Question

In SQL, is there a way to create a multi-column UNIQUE constraint for a formatted value?

In my case, I'm trying to prevent two items with the same title being created on the same day, using their created_at timestamp, without resorting to adding an additional column.

Something like the following.

CREATE TABLE daily_things (
  title text,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(title, created_at::date)
);

A similar question, but for single column https://stackoverflow.com/questions/55846991/applying-unique-constraint-of-date-on-timestamp-column-in-postgresql — Kevin Carmody, Dec 12 '19 at 14:59

score 1 · Accepted Answer · answered Dec 12 '19 at 15:05

You can use a unique index, but you must interpret the timestamp in a specific time zone to make the expression immutable (i.e., not depend on timezone):

CREATE UNIQUE INDEX ON daily_things
   (title, (CAST (created_at AT TIME ZONE 'UTC' AS date)));

In PostgreSQL, you can also use an exclusion constraint for that:

ALTER TABLE daily_things ADD EXCLUDE USING gist
   (title WITH =, CAST (created_at AT TIME ZONE 'UTC' AS date) WITH =);

That requires the btree_gist extension.

The unique index is most likely the better and faster method.

PostgreSQL multiple column UNIQUE on formatted value

1 Answers1