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
( booking_id int NOT NULL PRIMARY KEY,
check_in date NOT NULL,
check_out date NOT NULL,
booking_status int NOT NULL
) ;
CREATE TABLE
ALTER TABLE booking
ADD CONSTRAINT unique_booking
EXCLUDE
( check_in WITH =,
check_out WITH =,
(booking_status = 1 OR NULL) WITH =
)
DEFERRABLE INITIALLY DEFERRED ;
ALTER TABLE
INSERT INTO booking
VALUES
(1, '2016-01-01', '2016-01-20', 1),
(2, '2016-01-01', '2016-01-20', 2),
(3, '2016-01-01', '2016-01-20', 1), -- row inconsistent with the EXCLUDE constraint
(4, '2016-01-01', '2016-01-20', 2) ;
SELECT * FROM booking ; -- no problem because we haven't commited yet
DELETE FROM booking WHERE booking_id = 3 ;
DELETE 1
SELECT * FROM booking ;
| booking_id | check_in | check_out | booking_status |
|---|---|---|---|
| 1 | 2016-01-01 | 2016-01-20 | 1 |
| 2 | 2016-01-01 | 2016-01-20 | 2 |
| 4 | 2016-01-01 | 2016-01-20 | 2 |
SELECT 3