Thread: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

From

Dionisis Kontominas

Date:

26 July 2023, 12:00:13

Hello all,

In the Subject I mention what I am intending to do. Letme put some context; this is my table:

portal_user_role
(
f_id INTEGER NOT NULL,
f_portal_user_id INTEGER NOT NULL,
f_portal_role_id INTEGER NOT NULL,
f_is_active BOOLEAN NOT NULL,
f_is_deleted BOOLEAN NOT NULL,
f_start_date DATE NOT NULL,
f_end_date DATE,
f_created_on TIMESTAMP WITH TIME ZONE NOT NULL,
f_updated_on TIMESTAMP WITH TIME ZONE,
f_created_by CHARACTER VARYING(255) NOT NULL,
f_updated_by CHARACTER VARYING(255),
CONSTRAINT pk_portal_user_role PRIMARY KEY (f_id),
CONSTRAINT fk1_portal_user_role FOREIGN KEY (f_portal_user_id) REFERENCES portal_user (f_id),
CONSTRAINT fk2_portal_user_role FOREIGN KEY (f_portal_role_id) REFERENCES portal_role (f_id),
EXCLUDE USING gist (f_portal_user_id WITH =,
f_portal_role_id WITH =,
DATERANGE(f_start_date, COALESCE(f_end_date, 'infinity'), '[]') WITH &&)
);

So, this table has a range of dates [f_start_date, f_end_date] that I 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?

Also, should I post this question on pgsql-sql as more appropriate?

Thank you In Advance!

Regards,

Dionisis

Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

From

Thomas Kellerer

Date:

26 July 2023, 12:18:21

Dionisis Kontominas schrieb am 26.07.2023 um 11:00:
> Hello all,
>
> In the Subject I mention what I am intending to do. Letme put some context; this is my table:
>
> portal_user_role
> (
>     f_id INTEGER NOT NULL,
>     f_portal_user_id INTEGER NOT NULL,
>     f_portal_role_id INTEGER NOT NULL,
>     f_is_active BOOLEAN NOT NULL,
>     f_is_deleted BOOLEAN NOT NULL,
>     f_start_date DATE NOT NULL,
>     f_end_date DATE,
>     f_created_on TIMESTAMP WITH TIME ZONE NOT NULL,
>     f_updated_on TIMESTAMP WITH TIME ZONE,
>     f_created_by CHARACTER VARYING(255) NOT NULL,
>     f_updated_by CHARACTER VARYING(255),
>     CONSTRAINT pk_portal_user_role PRIMARY KEY (f_id),
>     CONSTRAINT fk1_portal_user_role FOREIGN KEY (f_portal_user_id) REFERENCES portal_user (f_id),
>     CONSTRAINT fk2_portal_user_role FOREIGN KEY (f_portal_role_id) REFERENCES portal_role (f_id),
>     EXCLUDE USING gist (f_portal_user_id WITH =,
>                         f_portal_role_id WITH =,
>     DATERANGE(f_start_date, COALESCE(f_end_date, 'infinity'), '[]') WITH &&)
> );
>
> So, this table has a range of dates [f_start_date, f_end_date] that I
> 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?

You can add a WHERE clause to the exclusion constraint (the condition must be enclosed in parentheses though):

    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) 

Note that you don't need COALESCE(f_end_date, 'infinity') because a daterange will treat null as infinity anyways.

Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

From

Dionisis Kontominas

Date:

26 July 2023, 12:25:46

Hi Thomas,

Thank you very much for your reply and comment.

I am trying to avoid writing trigger code to handle this requirement.

I will do so and try your suggestion.

I believe also that the partial constraint you propose to me should be in the end: ... WHERE (NOT f_is_deleted) as I do not want the deleted records to participate in the constraint logic.

Kindest regards,

Dionisis

On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <shammat@gmx.net> wrote:

Dionisis Kontominas schrieb am 26.07.2023 um 11:00:
> Hello all,
>
> In the Subject I mention what I am intending to do. Letme put some context; this is my table:
>
> portal_user_role
> (
> f_id INTEGER NOT NULL,
> f_portal_user_id INTEGER NOT NULL,
> f_portal_role_id INTEGER NOT NULL,
> f_is_active BOOLEAN NOT NULL,
> f_is_deleted BOOLEAN NOT NULL,
> f_start_date DATE NOT NULL,
> f_end_date DATE,
> f_created_on TIMESTAMP WITH TIME ZONE NOT NULL,
> f_updated_on TIMESTAMP WITH TIME ZONE,
> f_created_by CHARACTER VARYING(255) NOT NULL,
> f_updated_by CHARACTER VARYING(255),
> CONSTRAINT pk_portal_user_role PRIMARY KEY (f_id),
> CONSTRAINT fk1_portal_user_role FOREIGN KEY (f_portal_user_id) REFERENCES portal_user (f_id),
> CONSTRAINT fk2_portal_user_role FOREIGN KEY (f_portal_role_id) REFERENCES portal_role (f_id),
> EXCLUDE USING gist (f_portal_user_id WITH =,
> f_portal_role_id WITH =,
> DATERANGE(f_start_date, COALESCE(f_end_date, 'infinity'), '[]') WITH &&)
> );
>
> So, this table has a range of dates [f_start_date, f_end_date] that I
> 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?

You can add a WHERE clause to the exclusion constraint (the condition must be enclosed in parentheses though):

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)

Note that you don't need COALESCE(f_end_date, 'infinity') because a daterange will treat null as infinity anyways.

Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

From

Dominique Devienne

Date:

26 July 2023, 12:39:38

On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas@gmail.com> wrote:

On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <shammat@gmx.net> 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?

Just confirming, because I'm been considering a similar approach for storing chunks of large files (> 1GB),

to enforce those chunks don't overlap, per-"file". Seems ideal to enforce no-overlap, but OTOH,

you can't seem to see how to enforce "no-holes" for chunks. One concern is the cost of adding that

enforcement of no-overlap. Most "files" will be small (a few bytes to a single digit MBs), while some

definitely go into multi-GB territory. So how well do exclusion constraints scale to 100K or 1M rows?

What's their time-complexity? In other words, should "smaller" (i.e. < 1MB) "files" go into a separate

table w/o an exclusion constraint and w/o chunking, while only the larger ones go to the chunked table?

Thanks, --DD

[1]: https://www.postgresql.org/docs/current/btree-gist.html

Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

From

Thomas Kellerer

Date:

26 July 2023, 12:56:25

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.