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

回答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 overlapping
);

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

SQL Fiddle.

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.