I want to keep reservation dates in postgresql table column

Question

I have a table named villas and this table has a column named reserved_dates of type reserved_dates in daterange[]

I want to keep the booked dates in the reserved_dates column. Villas are booked between certain dates . For Example: Check In Date: 2023-02-05 Check Out Date: 2023-02-15. and in this case I can manually add {"[2023-02-05,2023-02-15)"} value to the reserved_dates column.

what i want is for example when client choose date

• Check In Date: 2023-02-10
• Check Out Date: 2023-02-20

I want to check, does the selected date range conflict with the one in the database?

And if there is no reservation date, I want to add it, how can I do that? Or what can I do for this problem?

I couldn't find the Result I wanted and the use of the new date types on many blog platforms, including the PostgreSQL 14 Documentation.

I am able to manually add the date range to reserved_dates. But I can't update the reservation date, if the reservations overlap

Answer 1

An exclusion constraint will do what you want. You have to include more than just the date range (1 room cannot be booked more than once on any given day but 2 separate rooms can be booked on the same day).

CREATE EXTENSION IF NOT EXISTS BTREE_GIST;
CREATE TABLE demo_table(
    RoomNumber INTEGER NOT NULL,
    CheckIn  DATE NOT NULL,
    CheckOut DATE  NOT NULL,
    CHECK (CheckIn  < CheckOut ),
    EXCLUDE USING gist (RoomNumber WITH =, daterange(CheckIn, CheckOut, '[)'::text) WITH &&)
);

The && operator is the range overlap (range1 && range2), which you can test in a regular SELECT query too.

EDIT: I have seen your comments.

Point 1: the devil is in the details. You named your table villas (plural), suggesting there is more than 1 villa to manage. In which case there should be an additional column to identify which villa is linked to a reservation (if not RoomNumber INTEGER, call it VillaName TEXT).
Honestly, even if you have only 1 villa, it would not hurt to plan for the future and make it so adding another one in the future does not require you to change your entire schema.

Point 2: I do not know why you would store the reservations in an array, it is probably a bad design choice (it will not let you use an index for instance, and delete past records as easily).
UNNEST is a quick fix for you. It turns your array elements into records.

Example:

SELECT *
FROM (
    SELECT UNNEST(reserved_dates) AS Reserved, [add other columns here]
    FROM villas
) R

But the correct way to do things, it was said in the comments, would rather be in the lines of:

CREATE TABLE villaReservation ([...]);
INSERT INTO villaReservation SELECT UNNEST(reserved_dates), ... FROM villas
WHERE Reserved && daterange('2023-03-10', '2023-02-20', '[)'::text)

Last thing: I personally prefer keeping the 2 bounds of ranges separate in tables (above, keep separate check-in and check-out dates).

1. It makes migrating from PostgreSQL to another DBMS easier (the table's CREATE script will not need to be adjusted).
2. It might not apply in your case but it makes it possible to have ranges in the form of [date1, date1), that is null ranges but with a placement in time.
   I actually encountered 1 use case where things needed to be saved this way, albeit in a different context than yours.