Table level constraint to prevent overlapping date ranges

Question

Below is the schema where I need to put a constraint such that a second new entry could be put in for a room number, even before the existing depDt for the same room number. Could any of you please help me with this??

CREATE TABLE Accomodation (
  roomNo INTEGER NOT NULL,
  arrDt DATE NOT NULL,
  depDt DATE NOT NULL,
  PRIMARY KEY (roomNo, arrDt), 
  CONSTRAINT date_chk CHECK (arrDt < depDt)
);

INSERT INTO HotelStays(roomNo, arrDt, depDt) VALUES 
  (123, to_date('20160202', 'YYYYMMDD'),to_date('20160206','YYYYMMDD')),
  (123, to_date('20160205', 'YYYYMMDD'), to_date('20160208','YYYYMMDD'));

I have tried giving a sub query under WHERE in CONSTRAINTS but its is not working in SQL Fiddle.

You're missing `)` at the end of second element in `INSERT` statement. — Kamil Gosciminski, Feb 20 '16 at 09:04
From what I see a second entry with the same room number __can__ be inserted as long as their date of arrival is different. What is your actual problem? Could you show the expected output and errors or the output you are receiving? — Kamil Gosciminski, Feb 20 '16 at 09:08
Please fix the naming of your columns as well. I doubt that `roomNum` and `roomNo` are any different. The same with `arrDt` and `arrDate` — Kamil Gosciminski, Feb 20 '16 at 09:10
follow the above link you will see the whole schema and table. same room number can be inserted as long as the date of arrival will not fall in between arrival date and departure date of the existing room number — Shashikiran, Feb 20 '16 at 09:32
You need a trigger to implement what you want. A `CHECK` constraint can look only at fields of the *same* row. Look [here](http://stackoverflow.com/questions/10179121/sql-sub-queries-in-check-constraint). — Giorgos Betsos, Feb 20 '16 at 10:03
@GiorgosBetsos: no you don't need a trigger. A simple exclusion constraint will do — , Feb 20 '16 at 10:24
@a_horse_with_no_name I didn't know such a thing existed in postgresql. — Giorgos Betsos, Feb 20 '16 at 10:34

score 2 · Answer 1 · answered Feb 20 '16 at 10:23

2

This can be done using an exclusion constraint on the date range:

alter table Accomodation
  add constraint no_overlap 
  exclude using gist (roomno with =, daterange(arrdt, depdt) with &&);

Note that you need the btree_gist extension to support the = operator in a GiST index.

answered Feb 20 '16 at 10:23

SQL Fiddle doesn't have this extension. – Shashikiran Feb 21 '16 at 03:00

Kamil Gosciminski · Answer 2 · 2016-02-20T10:10:23.047

1

Note: This does not solve the problem of race conditions.

Create a function that checks whether a room is available based on your conditions and returns a scalar boolean value which can be used in CHECK constraint.

Here you can preview how it works (remember to uncomment the last insert statement):SQL FIDDLE

CREATE FUNCTION is_room_available(int, date)
RETURNS boolean
STABLE
LANGUAGE plpgsql
AS 
$$
BEGIN
  IF EXISTS ( SELECT 1 FROM Accomodation WHERE roomNo = $1 AND $2 BETWEEN arrDt AND depDt ) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$$;

Create table with new constraint

CREATE TABLE Accomodation (
  roomNo INTEGER NOT NULL,
  arrDt DATE NOT NULL,
  depDt DATE NOT NULL,
  PRIMARY KEY (roomNo, arrDt), 
  CONSTRAINT date_chk CHECK (arrDt<depDt),
  CONSTRAINT room_avail CHECK (is_room_available(roomNo, arrDt)) -- added
  );

Try inserting two rows in separate statements

INSERT INTO Accomodation(roomNo, arrDt, depDt)
VALUES 
(123, to_date('20160202', 'YYYYMMDD'), to_date('20160206','YYYYMMDD'));

INSERT INTO Accomodation(roomNo, arrDt, depDt)
VALUES 
(123, to_date('20160205', 'YYYYMMDD'), to_date('20160208','YYYYMMDD'));

First value is inserted, while when issuing the second insert statement you get a check constraint violation

ERROR: new row for relation "accomodation" violates check constraint "room_avail" Detail: Failing row contains (123, 2016-02-05, 2016-02-08).

Note: This could be easily implemented using triggers as well. You just need to modify the function a little and issue a CREATE TRIGGER statement.

edited Feb 20 '16 at 10:10

answered Feb 20 '16 at 10:04

Kamil Gosciminski

16,547
8
49
72

What does $$ means? Why have you used GO terminator? – Shashikiran Feb 20 '16 at 10:14
@Shashikiran: the `$$` is called dollar quoting: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING – Feb 20 '16 at 10:24
@Shashikiran I used GO as terminator in SQL Fiddle to be able to use `;` inside a function without error. Normally this is the terminator there and would yield errors assuming for example `END IF;` to be invalid statement. – Kamil Gosciminski Feb 20 '16 at 10:37
it is not working for multiple insert statements INSERT INTO Accomodation(roomNo, arrDt, depDt) VALUES (123, to_date('20160202', 'YYYYMMDD'), to_date('20160206','YYYYMMDD')), (123, to_date('20160205', 'YYYYMMDD'), to_date('20160208','YYYYMMDD')) GO – Shashikiran Feb 21 '16 at 02:58
@Shashikiran I've mentioned that you need to insert two rows in separate statements in my answer. And why would your application build multiple inserts into one statement anyway? – Kamil Gosciminski Feb 21 '16 at 11:32
Sorry I forgot that, I was trying different things before incorporating in the code. And also could you please explain this SELECT query "SELECT 1 FROM Accomodation WHERE roomNo = $1 AND $2 BETWEEN arrDt AND depDt". I didn't understand $1 and $2 part – Shashikiran Feb 22 '16 at 04:52
`$1` and `$2` are variables storing input arguments for a function. Here `$1` is for `int` value, the first argument - which is a `roomNo` and `$2` is for `date` value - the date to be checked for overlaps. – Kamil Gosciminski Feb 22 '16 at 07:50
Thanks. I was little confused as I had to modify the depdate overlap constraint. Now I got it – Shashikiran Feb 22 '16 at 09:50

Table level constraint to prevent overlapping date ranges

2 Answers2