No overlapping data

Question

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.

Answer 1

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.