Search Postgresql Archives

Re: Order-independent multi-field uniqueness constraint?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux