From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wayne Cuddy <lists-pgsql@useunix.net>
cc: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: can this be done with a check expression?
In-reply-to: <20120802231043.GA16173@slacker.ja10629.home>
References: <20120802231043.GA16173@slacker.ja10629.home>
Comments: In-reply-to Wayne Cuddy <lists-pgsql@useunix.net>
	message dated "Thu, 02 Aug 2012 19:10:43 -0400"
Date: Thu, 02 Aug 2012 19:23:34 -0400
Message-ID: <29693.1343949814@sss.pgh.pa.us>

Wayne Cuddy <lists-pgsql@useunix.net> writes:
> I have a table with 3 columns:
> name text
> start_id integer
> end_id integer

> start_id and end_id are ranges which must not overlap but can have gaps
> between them. Is it possible to formulate a table check constraint that
> can verify that either id does not fall within an existing range at
> insert time? IE prevent overlaps during insert?

You can't do it reliably with a check constraint, at least not short of
taking table-wide locks to serialize all modifications of the table.
(If you were willing to do that, a check constraint calling a function
that does an EXISTS probe would work; although personally I'd use a
trigger instead.  Either way, performance is likely to suck.)

A less bogus way of doing things is to use an EXCLUDE constraint,
although that will restrict you to be running PG 9.0 or newer.  You
also need some way of representing the ranges as indexable objects.
In 9.0 or 9.1, probably the best way is to use contrib/seg/ to
represent the ranges as line segments.  9.2 will have a cleaner
solution, ie range types.

			regards, tom lane