How to declare a range overlapping constraint in PostgreSQL database?

Let's say we are having a table with this definition:

range (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  from bigint,
  to bigint
)

This table is actually a reduced view over enormously big table:

item (
  id bigint p开发者_StackOverflow社区rimary key,
  colourId int references colour(id),
  smellId int references smell(id),
  CONSTRAINT item_colour_smell_unique UNIQUE (colour, smell, id)
)

I would like to translate item_colour_smell_unique constraint in the range table. It should watch overlaps of ranges [from, to] while taking account of colourId and smellId column values.

Note that any trigger-based solution is inherently unsafe from race conditions, e.g. when two concurrent transactions insert a row with conflicting ranges, neither of them will see the other conflicting row, due to the "isolation" ACID property (only commited data can be seen).

Some solutions:

• Use procedures with explicit locking of the table to force serialization of inserts.
• Split the [from, to] range into [from, from+1, ..., to-1, to] and insert a row for each. This way you can use a simple UNIQUE INDEX on the "range" table.

PostgreSQL developer Jeff Davis has been writing about this lately and will implement range conflict constraints in PostgreSQL 8.5

There's no standard "overlapping" constraint. You will have to build your own from some triggers. There has been discussion of this for 8.5 though.

You might find the "seg" module useful too. See the manuals - Appendix F. Additional Supplied Modules

This doesn't fully give you an answer but it sounds like you might want to make use of a trigger.