Postgres constraint for unique datetime range

Question

My table has two columns:

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

I can't use range column - I have to operate on two columns: startsAt and endsAt. — user606521
– user606521, Commented Nov 4, 2014 at 13:22
Create a new column with datatype tsrange that takes startsAt and endsAt as input. A constraint on this new column fixes your problem. — Frank Heikens
– Frank Heikens, Commented Nov 4, 2014 at 13:26

Erwin Brandstetter · Accepted Answer · 2022-01-19 02:59:50Z

32

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}

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.

edited Jan 19, 2022 at 2:59

answered Nov 4, 2014 at 13:48

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Nicoletti Over a year ago

Could you describe how one could have the constraint tied to another column say 'client_id'?

Erwin Brandstetter Over a year ago

@DanielNicoletti: Yes. See stackoverflow.com/a/20513792/939860 or stackoverflow.com/a/27348912/939860

Collectives™ on Stack Overflow

Postgres constraint for unique datetime range

1 Answer 1

Add constraint to existing table

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Add constraint to existing table

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related