SQL NOT EXISTS ( BETWEEN (...) AND (...))

Question

I am creating a database that is for booking hotel rooms. I am stuck on a constraint that checks if a 'dateFrom' variable for a guest is between any 'dateFrom' and 'dateTo' variables for that specific guest. ie) a guest cannot book more than 1 room at a time.

I am getting an error: "cannot use subquery in check constraint":

CREATE TABLE tomsBooking 
(
    hotelNo HotelNo NOT NULL,
    guestNo INT NOT NULL,
    dateFrom DATE NOT NULL,
    dateTo DATE NOT NULL, 
    roomNo RoomNumber 

    CONSTRAINT GuestOverlap
    CHECK ( NOT EXISTS 
                (SELECT * FROM tomsBooking b 
                                WHERE b.guestNo = b.guestNo
                                AND b.dateTo >= dateFrom 
                                AND b.dateFrom <= dateTo
                            )
             )
);

@sstan Oh ok so would it be better to have a CONSTRAINT at the end of the booking table? — , Oct 17 '16 at 00:31
@sstan I have updated the question to use a CONSTRAINT instead but am still getting an error. I have updated the question with what I have now. — , Oct 17 '16 at 00:49
That kind of logic is still too complex for a check constraint. A check constraint can normally only validate a single row's data. I'm no PostgreSQL expert, but normally I would expect that this sort of validation logic can only be expressed in a trigger. — sstan, Oct 17 '16 at 00:55
`a guest cannot book more than 1 room at a time` - sounds like a strange requirement to me. Have you never heard of group bookings, where one person books all the rooms? — Tony, Oct 17 '16 at 00:55
You could use a trigger to block inserts that violate the logical constraint. — siride, Oct 17 '16 at 03:34
@sstan: this constraint can be expressed with an exclusion constraint in Postgres — , Oct 17 '16 at 06:08

score 4 · Answer 1 · answered Oct 17 '16 at 06:07

Unfortunately, Postgres does not support sub-queries for check constraints.

But this case is is exactly what exclusion constraints where created for:

CREATE TABLE tomsBooking 
(
    hotelNo HotelNo NOT NULL,
    guestNo INT NOT NULL,
    dateFrom DATE NOT NULL,
    dateTo DATE NOT NULL, 
    roomNo RoomNumber 
);

alter table tomsbooking
   add constraint guestoverlap 
   exclude using gist (guestno with =, daterange(datefrom, dateto) with &&);

For more details and examples, see the manual: https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT

In order for a GIST index to be able to use the = operator you need to install the btree_gist module using:

create extension btree_gist;

(That only needs to be done once per database)

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

Bearing in mind that I don't know Postgres SQL... it looks to me that you need the term VALUE near the BETWEEN statement so the comparison knows what value it is checking between.

As an alternative though, and based on Postgresql query between date ranges, I would structure it as this:

CREATE DOMAIN DateFrom AS DATE
    CHECK (VALUE > '2016-10-16' AND NOT EXISTS (SELECT * FROM tomsBooking b 
                            WHERE b.guestNo = g.guestNo
                                AND VALUE >= dateFrom 
                                AND VALUE <= dateTo
                            )
    );

As I say, I don't know Postgres, so you may have to tweak my suggestion.

edited May 23 '17 at 12:06

Community

1
1

answered Oct 17 '16 at 00:18

K Scandrett

16,390
4
40
65

I was looking through some online docs as well and I think your solution is better, but I still got the error: 'cannot use subquery in check constraint' – Oct 17 '16 at 00:24
1

@Thomas See comment by sstan. As the error says, you cannot have a subquery in a check constraint, but fortunately you do not need the check constraint as you should not be using DOMAIN for your purpose. – Basil Bourque Oct 17 '16 at 00:31

SQL NOT EXISTS ( BETWEEN (...) AND (...))

2 Answers2