Обсуждение: Order-independent multi-field uniqueness constraint?
I have a table used to store information about pairs of items. This information is independent of the order of the two items in the pair, so having two records X Y <info> Y X <info> in the table would be redundant. But as far as I can tell, this situation would not violate a uniqueness constraint involving the two fields. I could add the original constraint that enforces some canonical order, say X < Y (assuming that they are integer IDs), but I'm trying to avoid this because it would lead to a significant complication of many of my queries, which currently ascribe slightly different semantics to the first and second members of the pair. The only solution I could think of is to write a function that takes the two elements as input and returns them in some canonical order: CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement ) RETURNS anyarray AS $$ BEGIN IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ]; ELSE RETURN ARRAY[ $2, $1 ]; END IF; END; $$ LANGUAGE plpgsql; and this function works as expected, but when I try to use it in a constraint I get the error: -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); ERROR: 42601: syntax error at or near "(" LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); ^ LOCATION: base_yyerror, scan.l:795 I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK syntax but not UNIQUE(my_function(x)). But be that as it may, is there any way to enforce an order-independent uniqueness constraint without forcing a canonical ordering on the elements saved in the table. TIA! kj
Kynn Jones wrote: > I have a table used to store information about pairs of items. This > information is independent of the order of the two items in the pair, > so having two records > > X Y <info> > Y X <info> > > in the table would be redundant. But as far as I can tell, this > situation would not violate a uniqueness constraint involving the two > fields. > > I could add the original constraint that enforces some canonical > order, say X < Y (assuming that they are integer IDs), but I'm trying > to avoid this because it would lead to a significant complication of > many of my queries, which currently ascribe slightly different > semantics to the first and second members of the pair. > > The only solution I could think of is to write a function that takes > the two elements as input and returns them in some canonical order: > > CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement ) > RETURNS anyarray AS > $$ > BEGIN > IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ]; > ELSE RETURN ARRAY[ $2, $1 ]; > END IF; > END; > $$ LANGUAGE plpgsql; > > and this function works as expected, but when I try to use it in a > constraint I get the error: > > -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); > ERROR: 42601: syntax error at or near "(" > LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); > ^ > LOCATION: base_yyerror, scan.l:795 > > I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK > syntax but not UNIQUE(my_function(x)). > > But be that as it may, is there any way to enforce an > order-independent uniqueness constraint without forcing a canonical > ordering on the elements saved in the table. > I'm not sure that what you're doing is the best solution, but shouldn't that be: "... foo_uniq_x_y UNIQUE(SELECT canonicalize(x,y))"? brian
"Kynn Jones" <kynnjo@gmail.com> writes: > CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement ) > RETURNS anyarray AS > $$ > BEGIN > IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ]; > ELSE RETURN ARRAY[ $2, $1 ]; > END IF; > END; > $$ LANGUAGE plpgsql; You need to add IMMUTABLE as well. > and this function works as expected, but when I try to use it in a > constraint I get the error: > > -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); > ERROR: 42601: syntax error at or near "(" > LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); What you need is: CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y)); > LOCATION: base_yyerror, scan.l:795 > > I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK > syntax but not UNIQUE(my_function(x)). Really? It doesn't work for me in the ADD CONSTRAINT syntax. I don't think you can use the ADD CONSTRAINT syntax, you have to use the CREATE UNIQUE INDEX syntax. It's effectively the same in Postgres anyways. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On 10/19/07, Gregory Stark <stark@enterprisedb.com> wrote: > "Kynn Jones" <kynnjo@gmail.com> writes: > > > CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement ) > > RETURNS anyarray AS > > $$ > > BEGIN > > IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ]; > > ELSE RETURN ARRAY[ $2, $1 ]; > > END IF; > > END; > > $$ LANGUAGE plpgsql; > > You need to add IMMUTABLE as well. > > > and this function works as expected, but when I try to use it in a > > constraint I get the error: > > > > -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); > > ERROR: 42601: syntax error at or near "(" > > LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); > > What you need is: > > CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y)); Yep, that did the trick. > > I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK > > syntax but not UNIQUE(my_function(x)). > > Really? It doesn't work for me in the ADD CONSTRAINT syntax. My mistake, sorry. I was probably misremembering something I saw in a CREATE INDEX statement. Thanks! kj