I have a Booking table composed by these attributes: Booking(ID, checkIn, checkOut)
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 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;