Hacker News new | comments | show | ask | jobs | submit login

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.

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 &&)

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.)

What 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)

Applications are open for YC Summer 2018

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact