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)
);
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.
