By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE Booking
(ID bigint PRIMARY KEY,
checkin timestamp(0) NOT NULL, checkout timestamp(0) NOT NULL)
;
INSERT INTO Booking
(ID, checkin, checkout)
VALUES
(1, '2022-12-03 23:59:59', '2022-12-07 23:59:59');
INSERT 0 1
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'),
(1, '2022-12-10 23:59:59', '2022-12-11 23:59:59');
INSERT 0 3
SELECT * FROM Booking;
| id | checkin | checkout |
|---|---|---|
| 1 | 2022-12-03 23:59:59 | 2022-12-07 23:59:59 |
SELECT 1
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;
INSERT 0 1
SELECT * FROM Booking;
| id | checkin | checkout |
|---|---|---|
| 1 | 2022-12-03 23:59:59 | 2022-12-07 23:59:59 |
| 3 | 2022-12-08 23:59:59 | 2022-12-09 23:59:59 |
SELECT 2