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

Answer

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.