CODEWITHSUNDEEP
sqlpostgresqldatabase-designconstraintsmutual-exclusion
user606521
user606521

Reputation: 15434

Postgres constraint for unique datetime range

My table has two columns:

  1. startsAt
  2. endsAt

Both hold date and time. I want to make following constraint:

IF both columns are NOT NULL then range between startsAt and endsAt must not overlap with other ranges (from other rows).

Upvotes: 18

Views: 8446

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656992

You can keep your separate timestamp columns and still use an exclusion constraint on an expression:

CREATE TABLE tbl (
   tbl_id    serial PRIMARY KEY
 , starts_at timestamp
 , ends_at   timestamp
 , EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)  -- no overlap
);

Constructing a tsrange value without explicit bounds as tsrange(starts_at, ends_at) assumes default bounds: inclusive lower and exclusive upper - '[)', which is typically best.

db<>fiddle here
Old sqlfiddle

Related:

Add constraint to existing table

ALTER TABLE tbl ADD CONSTRAINT tbl_no_overlapping_time_ranges
EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)

Syntax details are the same as for CREATE TABLE.

Upvotes: 32

Related Questions