CODEWITHSUNDEEP
postgresqlindexing
Reinderien
Reinderien

Reputation: 15233

PostgreSQL unique index based on a ranged numeric field

I have a scheduleitem table with columns for room, dayofweek, starttime and endtime. I'd like to be able to create a unique index on room and dayofweek where the predicate (somehow) prevents the table from containing overlapping times, that is, prevent overlap where a potential new row r2 and any given existing row r1 satisfy

r2.endtime > r1.starttime and r1.endtime > r2.starttime

So far I can't add a subquery to the partial index predicate, and any stored procedure that I reference in the list of unique columns has to be immutable (can't run any queries), so I'm stumped.

Upvotes: 2

Views: 414

Answers (2)

Magnus Hagander
Magnus Hagander

Reputation: 25098

You are looking for the feature EXCLUSION CONSTRAINTS - see http://www.postgresql.org/docs/9.0/static/sql-createtable.html and look at the part about EXCLUDE.

Upvotes: 5

dcp
dcp

Reputation: 55449

Why not just use a trigger? You can read about them here.

Upvotes: 1

Related Questions