2

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.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
Berethor
  • 343
  • 2
  • 13
  • I'm assuming when you say "within X months" that "X" actually has some fixed value for the application. To make the SQL more concrete, I've assumed "within 6 months" for my answer. – IMSoP Sep 10 '21 at 10:51

2 Answers2

4

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

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • looks like exactly what i need. i will check this out soon and mark this answer, thanks – Berethor Sep 10 '21 at 11:22
  • I am getting the following error: data type character varying has no default operator class for access method "gist" Hint: You must specify an operator class for the index or define a default operator class for the data type. here is the sql: ```alter table elements add constraint dedup_constraint exclude using gist ( type with =, name with =, tsrange(date, date + interval '3 months') with && ); ``` – Berethor Sep 12 '21 at 13:08
  • What Postgres version are you using? As you can see, I got it to work in an online demo of Postgres 13. – IMSoP Sep 12 '21 at 13:19
  • PostgreSQL 13.3 – Berethor Sep 12 '21 at 16:06
  • found the issue. need to install extenstion btree_gist ``` CREATE EXTENSION btree_gist; ``` – Berethor Sep 12 '21 at 16:10
  • i wonder if you are up for the challenge :) what if i had a logical "deleted" field, where the constraint should be ignored ? – Berethor Sep 12 '21 at 17:12
  • solved this one as well :) added WHERE (NOT deleted) to the constraint – Berethor Sep 12 '21 at 18:07
0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The question seemed clear to me, and I don't think this approach works: 2020-12-15 and 2021-02-01 are "within three months of each other", but will not have the same value if truncated to the nearest quarter. – IMSoP Sep 10 '21 at 11:01