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

user_1892297 0
Viewed 308

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.

postgresql sql
2 Answers

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

user_1579570

Your question is a bit vague. And the following does not answer it 100%, but you can create a unique index on an expression. So, you can prevent two rows from being in the same calendar month by using:

create unique index unq_elements_name_type_month
    on (name, type, date_trunc('month', date));

Quarters would also be easy, you can use 'quarter' instead of 'month'. You could extend this using arithmetic to any number of months.

This doesn't exactly answer the question you asked. But it might be sufficient for the problem you want to solve.

user_11440350
Related