From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint? |
Date: | 2023-07-26 09:56:25 |
Message-ID: | 8139813e-6c63-b5a1-2127-c64b2bd09074@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dominique Devienne schrieb am 26.07.2023 um 11:39:
> On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas(at)gmail(dot)com <mailto:dkontominas(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Praneel Devisetty | 2023-07-26 12:03:50 | Query on Primary_conninfo |
Previous Message | Dominique Devienne | 2023-07-26 09:39:38 | Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint? |