No overlapping data

Question

Being a new user of postgres, I have created a database in postgres 13. It contains tables including 4 Fields

ID integer (PK)
HoleID varchar(20)
From numeric NOT NULL CHECK (From>=0)
To numeric
Cat varchar (20)

I want to create a constraint that will check that for an identical entity number, the From and To fields of a record must not overlap with another record.

I have proposed the exclude constraint below but it does not work

ADD CONSTRAINT no_overlap EXCLUDE USING GIST ("HoleID" WITH =, ("mFrom", "mTo") WITH &&);

Thank you for helping me.

Consider using a single [`numrange`](https://www.postgresql.org/docs/13/rangetypes.html#RANGETYPES-BUILTIN) instead of from/to. — Schwern, Mar 06 '22 at 20:11

score 1 · Answer 1 · answered Mar 06 '22 at 20:25

1

This is easier with a single numrange column instead of from/to.

create table thing (
  id bigserial primary key,
  holeid varchar(20),
  range numrange not null,
  exclude using gist (range with &&, holeid with =)
);

insert into thing (holeid, range) values
  ('foo', '[1, 10]'::numrange),
  -- ok, same holeid, does not overlap
  ('foo', '[10.1, 11]'::numrange),
  -- ok, different holeid, overlaps.
  ('bar', '[2,5]'::numrange),
  -- not ok, same holeid, overlaps.
  ('foo', '[0, 1]'::numrange);

Demonstration.

Note that [] is inclusive and () is exclusive. (1,10)::numrange and (10,20)::numrange do not overlap. [1,10]::numrange and [10,20]::numrange do overlap.

answered Mar 06 '22 at 20:25

Schwern

153,029
25
195
336

Ok, it's clear but my database should absolutely contains From and To fields. – Lamethode Mar 06 '22 at 21:16
So is it possible to create a generated numrange column which will use From and to field? If yes how – Lamethode Mar 06 '22 at 21:21
@Lamethode Consider whether you really need those colums, or if `select lower(range) as "from"` is sufficient. You can make a generated column with `"from" numeric generated always as (lower(range)) stored` or you can make a view `create view as select lower(range) as "from"`. Note that `from` and `to` are SQL keywords and poor choices for column names. Consider something more descriptive of what the range actually is. – Schwern Mar 06 '22 at 22:01

score 0 · Accepted Answer · answered Mar 07 '22 at 08:48

0

You can create the constraint like this:

ALTER TABLE tab
ADD CONSTRAINT no_overlap EXCLUDE USING gist (
   "HoleID" WITH =,
   numrange("mFrom", "mTo") WITH &&
);

You should never store a timestamp as number.

answered Mar 07 '22 at 08:48

Laurenz Albe

209,280
17
206
263

No overlapping data

2 Answers2