postgresqldatabase-design

Ensure uniqueness in join table with start/end dates


Let’s say a PostgreSQL database has three tables, users, entities and connections, where the latter is a join table to keep track of which users are connected to which entities (so it has the expected user_id and entity_id columns).

Users can have multiple (past) connections to an entity, and I want to keep track of these, but they should only be allowed to have one active connection. By "active" I mean that the current time is between connections.valid_from and connections.valid_to (or something similar - if there is a better way to model this, I’m all ears). It would also be preferable if overlapping periods were not allowed for any user/entity combination.

I could of course ensure that these constraints are met at the application level, but I would much prefer to do it at the database level.

My understanding is that using now() is impossible in a unique index, so I’m guessing I would need something more advanced, like a trigger?

What is the best way to model something like this?


Solution

  • Instead of from/to, use a single tsrange column. Ranges have a lot of built in operators which make checking for overlap easy, like &&. Then add an exclusion constraint on any overlapping timestamp ranges.

    CREATE TABLE connections (
        ...
        
        valid_during tsrange not null,
        EXCLUDE USING GIST (valid_during WITH &&)
    );