Order-independent multi-field uniqueness constraint?

From: "Kynn Jones" <kynnjo(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Order-independent multi-field uniqueness constraint?
Date: 2007-10-19 17:52:42
Message-ID: c2350ba40710191052s4bd51716j10ba9d14be9f06fa@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Meskes 2007-10-19 18:04:27 Re: Abbreviation list
Previous Message smiley2211 2007-10-19 17:44:19 Re: Generating sql to capture fully qualified table names??? - THANKS