Hacker News Reader

Comment by mcny | Hacker News Reader

Let’s say I have a table for reservations: 1. Item ID 2. Reservation duration

What I would like is if I have an item ID of 1 and a reservation duration of today 10AM to 11AM and I try to add another record of item ID 1 and duration 10:39AM to 11:39AM the insert should fail.

Apparently it is straightforward in db2 but not in postgresql?

View on HN

Postgresql supports storing range of values, in particular ranges of timestamps.

I never used them but the manual page shows an example on avoiding overlaps that looks a lot like your case:

https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPE...

Something like this will do the job in PostgreSQL:

  CREATE TABLE reservations (
    item_id uuid,
    duration tsrange,
    EXCLUDE USING GIST (item_id WITH =, duration WITH &&)
  );

Can this be made to work if the `id` and `duration` are in different tables (with a joining key)?

Reply on news.ycombinator.com