[SQL] can this be done with a check expression?
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? Thanks, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] can this be done with a check expression?
Wayne Cuddy 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] can this be done with a check expression?
Tom Lane wrote:
> Wayne Cuddy writes:
> 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.
Simple example for 9.2:
test=# create table foo (name text, id_range int4range, exclude using gist(name
with =, id_range with &&));
NOTICE: CREATE TABLE / EXCLUDE will create implicit index
"foo_name_id_range_excl" for table "foo"
CREATE TABLE
Time: 40,273 ms
test=*# insert into foo values ('name1', '[1,9)');
INSERT 0 1
Time: 0,660 ms
test=*# insert into foo values ('name1', '[10,19)');
INSERT 0 1
Time: 0,313 ms
test=*# insert into foo values ('name1', '[5,15)');
ERROR: conflicting key value violates exclusion constraint
"foo_name_id_range_excl"
DETAIL: Key (name, id_range)=(name1, [5,15)) conflicts with existing key
(name, id_range)=(name1, [1,9)).
test=!#
Great feature! Thx to all developers behing PG!
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
