Postgres constraint for unique datetime range

by

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:

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.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. Create unique constraint with null columns
  3. Custom SERIAL / autoincrement per group of values
  4. Safely rename tables using serial primary key columns
  5. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  6. Serial numbers per group of rows for compound key
  7. PostgreSQL: Which Datatype should be used for Currency?
  8. Perform this hours of operation query in PostgreSQL
  9. NOT NULL constraint over a set of columns
  10. Continuing a transaction after primary key violation error
  11. How to write a constraint concerning a max number of rows in postgresql?
  12. Select (retrieve) all records from multiple schemas using Postgres
  13. Which datatype should be used for currency?
  14. Use email address as primary key?
  15. PostgreSQL Index on JSON
  16. Temporal database design, with a twist (live vs draft rows)
  17. PostgreSQL “Column does not exist” but it actually does
  18. Is SELECT or INSERT in a function prone to race conditions?
  19. Index for finding an element in a JSON array
  20. Database design for a survey [closed]
  21. Turn off constraints temporarily (MS SQL)
  22. Double colon `::` notation in SQL
  23. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  24. Export specific rows from a PostgreSQL table as INSERT SQL script
  25. PostgreSQL Where count condition
  26. How to return two columns with function
  27. Composite Primary Keys : Good or Bad?
  28. Prepared Statement on Postgresql in Rails
  29. Historical / auditable database
  30. PostgreSQL: top n entries per item in same table

Leave a Comment