Обсуждение: 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