Create table with date/time range constraint to prevent overlapping

Question

I'm trying to create a table in PostgreSQL, preventing users from entering an action that takes time during an action that was inserted before.

My objective is to create a hospital schema, and prevent user from adding an operation that takes time during operation that was added before, that's happening in the same room (no overlapping).

Let's say, first, I've added an operation in room 'a1' that takes time between 15:45 and 16:45, Nov 15th then I would like the database to prevent me from adding an operation in room 'a1' that takes time between 16:00 and 17:00, Nov 15th.

Something like (not working):

CONSTRAINT operation_un UNIQUE(room, ???datetime???)

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Apr 16 '16 at 21:14

score 3 · Accepted Answer · answered Apr 16 '16 at 21:48

3

The phrase you want to google for is "postgresql exclusion constraint".

There is an example doing exactly what you want here.

http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT

answered Apr 16 '16 at 21:48

Richard Huxton

21,516
3
39
51

Very slick - wish SQL Server had such a constraint – Clay Apr 16 '16 at 21:54
SQL Server has its own strong points, but PostgreSQL has some sophisticated type support. – Richard Huxton Apr 16 '16 at 21:56
You can do something similar in SQL Server - see http://stackoverflow.com/questions/12035747/date-range-overlapping-check-constraint – Paul Hatcher Oct 06 '16 at 13:18

Create table with date/time range constraint to prevent overlapping

1 Answers1