Postgres constraint for unique datetime range

Question

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).

Answer 1

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:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

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.