3 ms·
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 toda
by mcny 7y ago
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?
- mrighele 7y agoPostgresql 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#RANGETYPES-CONSTRAINT https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPE...
- woutgaze 7y agoSomething like this will do the job in PostgreSQL: CREATE TABLE reservations ( item_id uuid, duration tsrange, EXCLUDE USING GIST (item_id WITH =, duration WITH &&) );
- nicoburns 7y agoCan this be made to work if the `id` and `duration` are in different tables (with a joining key)?