I have to create a query that check before inserting if the booking dates don't overlap

Question

I have a Booking table composed by these attributes: Booking(ID, checkIn, checkOut)

ID bigint NOT NULL,
checkIn timestamp(0) NOT NULL,
checkOut timestamp(0) NOT NULL,
primary key(ID)

timestamp(0) datatype is something like this: '2022-12-31 23:59:59'.

and I need to create an INSERT query that checks before inserting if it doesn't exist another query overlapping those checkIn/checkOut dates.

For example I already have in my db this tuple: (1, '2022-12-03 23:59:59', '2022-12-07 23:59:59')

If I insert this tuple (2, '2022-12-04 23:59:59', '2022-12-06 23:59:59') there are no problems for the DB, because ID is unique, but I cannot have two bookings in the iteraval from 12/04 - 12/06

This expression checks if two interval don't overlap (checkIn1 <= checkOut2) and (checkOut1 >= checkIn2)

I'm using PostgreSQL.

Thank you very much

You need to provide us with a lot more information about your database and your schema, including what you have tried so far, before we can provide an answer. SO is not here to solve homework. — justhere, May 31 '22 at 08:57

score 0 · Answer 1 · answered May 31 '22 at 09:44

0

You can create a constraint on CHECKING and CHECKOUT values, this will allow only one unique set to be inserted, the next insertion with duplicate data will fail:

ALTER TABLE BOOKING ADD CONSTRAINT const_BOOKING UNIQUE (CHECKIN, CHECKOUT)

Test in fiddle

More about constraint here

answered May 31 '22 at 09:44

Hana

824
4
14
30

I'm sorry Hana, but that's not what I mean, of course I cannot insert two dates who are the same, the problem is that if I already have a booking (1, 'May 5', 'May 9'), I couldn't insert something like this (2, 'May 6', 'May 8'), because the "Flight" it's already booked in those dates. – Mohammed Kumar May 31 '22 at 10:14

score 0 · Accepted Answer · answered Jun 01 '22 at 05:49

You can create a temporary table and enter the data or rows in that table first.

CREATE TABLE Table2
(ID bigint PRIMARY KEY,
checkin timestamp(0) NOT NULL, 
checkout timestamp(0) NOT NULL);
    
INSERT INTO Table2
(ID, checkin, checkout)
VALUES
(2, '2022-12-04 23:59:59', '2022-12-06 23:59:59'),
(3, '2022-12-08 23:59:59', '2022-12-09 23:59:59');

Now you can use this table to insert the data in to the Original table using the JOIN clause:

INSERT INTO Booking (id, checkin, checkout)
SELECT t2.* 
FROM Table2 t2
LEFT JOIN Booking t3
ON t2.id <> t3.id AND (t3.checkin > t2.checkout OR t3.checkout < t2.checkin)
WHERE t3.id IS NOT NULL;

db fiddle link

OMFGTYSM . This is what I was looking for. – Mohammed Kumar Jun 01 '22 at 06:28 — Mohammed Kumar, Jun 01 '22 at 06:28

I have to create a query that check before inserting if the booking dates don't overlap

2 Answers2