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 recordsX 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
