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

1. ID integer (PK)
2. HoleID varchar(20)
3. From numeric NOT NULL CHECK (From>=0)
4. To numeric
5. 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.

CodePudding user response：

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.

CodePudding user response：

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.