psql - How to exclude overlapping timerange but accept common bound?

Question

I want to prevent insertion of overlapping timerange but accept a common bound. Given the below timerange (type TSRANGE) :

[01-01-2021 10:00:00, 01-01-2021 11:00:00]

I want this timerange to be accepted :

[01-01-2021 11:00:00, 01-01-2021 12:00:00]

But this one to be excluded :

[01-01-2021 09:00:00, 01-01-2021 10:30:00]

Is there a simple way to do this with gist ? For now I use the restriction from the doc :

EXCLUDE USING gist ... WITH &&

But the one I want to insert is excluded.

ANSWER

Answering my own question. The gist is actually fine. When inserting a new time range I was using :

INSERT INTO ... tsrange('2021-01-01 10:00', '2021-01-01 11:00', '[]')

Use '()' instead of '[]' if you want to set different exclusion.

question from:https://stackoverflow.com/questions/65919693/how-to-exclude-overlapping-timerange-but-accept-common-bound

Answer 1

Waitting for answers