Re: Order-independent multi-field uniqueness constraint?

"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