Index to enforce non-overlapping ranges?

From: James Robinson <jlrobins(at)socialserve(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Index to enforce non-overlapping ranges?
Date: 2008-05-08 13:27:52
Message-ID: 69830892-65A1-49DD-B638-5D6A7F6F3F5B@socialserve.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Academic question here:

Given a table with a pair of any sort of line-segment-esqe range
delimiter columns, is it possible to build a unique index to enforce
non-overlapping ranges? Such as:

create table test
(
id int not null primary key,
low_value int not null,
high_value int not null
);

Can one build an index to enforce a rule such that no (low_value,
high_value) range is identical or overlaps with another (low_value,
high_value) range described by the table? And, more interestingly,
what about for ranges of dates / timestamps as opposed to simple
integers?

I can see how a trigger on insert or update could enforce such a
constraint [ probe the table for an existing overlapping row, and
raise exception one exists ], but can such an activity be performed
with fewer lines using some sort of r-tree index?

----
James Robinson
Socialserve.com

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Chris Browne 2008-05-08 14:31:25 Re: Index to enforce non-overlapping ranges?
Previous Message Matthew T. O'Connor 2008-05-08 05:21:52 Re: Joining with result of a plpgsql function