0

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?

1
  • Why the index must be unique?
    – McNets
    Aug 15, 2019 at 12:56

1 Answer 1

0

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

1
  • Love this solution!
    – Gajus
    Aug 15, 2019 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.