2

PostgreSQL(9.6), I am trying to check whether a date interval

('2018-11-18 12:00','2018-11-20 12:00')

does not overlap any of the following date intervals in the array

{('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00')}

The elements in the array are composite:

CREATE TYPE reservation AS (
checkIn TIMESTAMP WITHOUT TIME ZONE,
checkOut TIMESTAMP WITHOUT TIME ZONE
)

Basically I am trying to check whether a "room" is available for a selected time interval by checking if this interval does not overlap with any existing reservation date intervals, example: ('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00').

Here is the rooms table:

CREATE TABLE rooms (
rId roomId PRIMARY KEY,
hRef hotelId NOT NULL,
rNo roomNo NOT NULL,
rType roomType,
numPeople INTEGER,
rBedOptions roomBed[],
reservations reservation[],
priceNight FLOAT,
FOREIGN KEY (hRef) REFERENCES hotels(hId) ON UPDATE CASCADE ON DELETE SET NULL  
)

INSERT INTO rooms VALUES
('R001','H001','101','one-bedroom',1,
ARRAY[row('1 twin')::roomBed],
ARRAY[
      row('2018-11-21 12:00','2018-11-23 12:00')::reservation,
      row('2018-11-19 12:00','2018-11-20 12:00')::reservation],
450.5);

So far I have been successful in checking the first element of the array for each row by writing the following query:

SELECT * FROM rooms R 
WHERE R.reservations[1] IS null 
OR NOT (('2018-11-18 12:00','2018-11-20 12:00') 
OVERLAPS (R.reservations[1].checkIn, R.reservations[1].checkOut)) 
ORDER BY rid;

The problem is that I don't know how to check all elements in the array if there is more than one. Any ideas or suggestions? Thanks

  • how to check all elements in the array if there is more than one Split the array to the recordset (UNNEST() function). – Akina Nov 18 '18 at 15:51
0

Why don’t you use second table “rooms_reservations” with period of data reservation? It’s more easy and faster with big data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.