How to make a unique constraint on date that there is no row with date within x months

I have a table that has a timestamp column something like:

table Elements

id: uuid
date: timestamp
name: varchar
type: varchar

I would like to put a unique constraint on the table that will make sure that there are NO two entries with the same name and type and are within X months of any other row. I have asynchronous processes that push rows into this table, and doing a select before can fail due to race conditions.

Answer

You can achieve this with an “exclusion constraint”, which is like a generalised unique constraint which can check any operator. See this question for some background on the syntax.

In particular, we can say that no two rows A and B should exist where the following conditions hold:

• A.name equals B.name
• A.type equals B.type
• A.date is between B.date and B.date + 6 months

Note that you don’t also need to check the 6 months before B.date, because that will checked by looking at the rows the other way around: B.date will be between A.date and A.date + 6 months.

To make the last condition implementable with a single operator, we can express it in terms of ranges:

• The range A.date to A.date + 6 months overlaps the range B.date to B.date + 6 months

We can then write an exclusion constraint which analyses using the && (range overlap) operator, which looks like this:

Alter Table entries
   Add Constraint name_and_type_within_6_months
   Exclude Using Gist (
       name with =,
       type with =,
       tsrange(date, date + interval '6 months') with &&
   );

(Hat tip to Philipe Fatio for this gist showing a date range exclusion.)

Here is an interactive demo showing that constraint in action: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=83181388416d1e5905e088532839ad79