I have a table that records observed movies in a cinema:
CREATE TABLE cinema_movie_name ( id integer DEFAULT nextval('cinema_movie_name_id_seq'::regclass) PRIMARY KEY, cinema_id integer NOT NULL REFERENCES cinema(id) ON DELETE CASCADE ON UPDATE RESTRICT, name text NOT NULL, movie_id integer REFERENCES movie(id) ON DELETE SET NULL ON UPDATE RESTRICT, created_at timestamp with time zone NOT NULL DEFAULT now() );
Every time we see a new movie name in the cinema, we record it in this table and map it to
movie_id in our database.
The current uniqueness is enforced on
(cinema_id, name), i.e.
CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx ON cinema_movie_name(cinema_id, name);
The problem is that over a long time, there can be multiple different movies with the same name, i.e. after some arbitrary time duration
(id, cinema_id, name, movie_id) becomes invalid.
Therefore, I need to create an index that enforces a unique constraint on
(cinema_id, name) and
created_at ±3 month.
How to create a unique index with a temporal column?