3 ms·
Couldn't you just have a constraint based on more then one column? In fact for many applications there are different types of resources (rooms, people, etc) wh
by mjevans 10y ago
Couldn't you just have a constraint based on more then one column? In fact for many applications there are different types of resources (rooms, people, etc) which you might want to be unique within anyway.
- colanderman 10y agoEXCLUDE constraints require the range to be a single value. (You could EXCLUDE over an expression based on both columns but it's nicer to have one.)
- infogulch 10y agoWhat do you mean by nicer? Not all drivers support range. Why is it better than an exclusion over an expression with two columns? (with simpler data types that the driver certainly supports)
- jeltz 10y agoYou still would need PostgreSQL's exclusion constraints to handle overlapping ranges since unqiue constraints do not handle checking for overlapping ranges, only exact equality. The below example guarantees that the same room can only be booked once. CREATE TABLE reservations ( room text, during tstzrange, EXCLUDE USING gist (room WITH =, during WITH &&) ); You could also still have two columns and use an expression. CREATE TABLE reservations ( room text, start timestamptz, stop timestamptz, EXCLUDE USING gist (room WITH =, tstzrange(start, stop, '[]') WITH &&) );