Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 =
          ) 
        WHERE (booking_status = 1)
        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

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