[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?
From:       Thomas Kellerer <shammat () gmx ! net>
Date:       2023-07-26 9:56:25
Message-ID: 8139813e-6c63-b5a1-2127-c64b2bd09074 () gmx ! net
[Download RAW message or body]

Dominique Devienne schrieb am 26.07.2023 um 11:39:
> On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas@gmail.com \
> <mailto:dkontominas@gmail.com>> wrote: 
> Dionisis Kontominas schrieb am 26.07.2023 um 11:00:
> > do not want two records to overlap, for the same user, the same role
> > and also when the f_is_deleted is TRUE only.
> > I do not care for the records when the f_is_deleted is FALSE on them; i.e. they \
> > should not be part of the restriction/constraint.   How can I achieve this?
> 
> EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =, \
> DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted) 
> 
> But that requires the btree_gist extension [1] extension, no?
> 

Yes, but that would also be the case if you didn't include the WHERE clause.

The "WITH =" is the reason you need the btree_gist extension.

> So how well do exclusion constraints scale to 100K or 1M rows?
> What's their time-complexity?

They are using a GIST index, so I would expect all restrictions and advantages that \
apply to GIST indexes in general, also apply to exclusion constraints.

The main drawback is most probably the slower update compared to a Btree index.

Unless you have a really high update frequency, I wouldn't worry about that for such \
a small table.


[prev in list] [next in list] [prev in thread] [next in thread]