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?
Date: Thu, 02 Aug 2012 19:23:34 -0400
Message-ID: <29693.1343949814@sss.pgh.pa.us> (raw)
In-Reply-To: <20120802231043.GA16173@slacker.ja10629.home>
References: <20120802231043.GA16173@slacker.ja10629.home>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, lists-pgsql@useunix.net
Subject: Re: can this be done with a check expression?
In-Reply-To: <29693.1343949814@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox