How to declare a range overlapping constraint in PostgreSQL database?

Question

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

intgr · Accepted Answer · 2009-11-14 17:28:00Z

3

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

answered Nov 14, 2009 at 17:28

intgr

20.7k6 gold badges63 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oroboros102 Over a year ago

Range conflic constraints were added, see postgresql.org/docs/current/…

Richard Huxton · Accepted Answer · 2009-11-06 15:46:07Z

1

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

answered Nov 6, 2009 at 15:46

Richard Huxton

541 bronze badge

Comments

Kuberchaun · Accepted Answer · 2009-11-05 13:13:00Z

0

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

answered Nov 5, 2009 at 13:13

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Collectives™ on Stack Overflow

How to declare a range overlapping constraint in PostgreSQL database?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related