How to design a table/ unique index where uniqueness is temporal?

Question

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?

Answer 1

I would add a column is_current or something similar that indicates if the movie is a "current" one, then you can create a unique index on only the "current ones"

CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx 
    ON cinema_movie_name(cinema_id, name)
where is_current;

You can have a regular job that sets old movies to is_current = false