Re:Why Use Postgres?
by mjevans on Sunday April 30, 2017 @03:53PM
(#14233490)
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.
[ Parent
| on HN ]
You 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 &&)
);
Re:Why Use Postgres?
by colanderman on Sunday April 30, 2017 @05:25PM
(#14233934)
EXCLUDE 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.)