Postgres restriction for unique datetime range

Question

My table has two columns:

• startsAt
• endsAt

Both have a date and time. I want to make the following restriction:

If both columns are NOT NULL, then the range between startAt and endsAt should not overlap with other ranges (from other rows).

Answer 1

You can keep your individual timestamp columns and still use the exception constraint in the 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 ); 

Building a tsrange value without explicit boundaries like tsrange(starts_at, ends_at) automatically accepts standard boundaries: including the lower and upper ones, with the exception of '[)' , which is usually best.

SQL Fiddle

on this topic:

• Prevent contiguous / overlapping records 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 &&) 

The syntax details are the same as for CREATE TABLE .