Exclude constraint on ranges : commutative containment : allow only complete containment

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Exclude constraint on ranges : commutative containment : allow only complete containment
Date: 2020-01-29 16:12:20
Message-ID: 3b555944-1563-6614-a5d5-e0cc001aecdb@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello Dear Postgresql ppl,
I have a table with date ranges and need to express the following constraint : allow overlaps only if there is complete containment, e.g.
allow values in rows like :
[2020-01-01,2020-01-31)
[2020-01-02,2020-01-10)
[2020-01-10,2020-01-20)

but disallow rows like

[2020-01-02,2020-01-10)
[2020-01-08,2020-01-11)

I think that writing a new commutative range operator e.g. |<@@> which would return true if the left operand is either contained by or contains the right operand and false otherwise would solve this,
I am just wondering if there is a more elegant and economical way to express this. (besides writing a trigger which is always an option).|

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Perumal Raj 2020-01-29 17:39:03 Performance Issue after upgrade from 9 to 11
Previous Message Thomas Munro 2020-01-29 11:26:00 Re: ERROR: too many dynamic shared memory segments