Re: Order-independent multi-field uniqueness constraint?

"Kynn Jones" <kynnjo(at)gmail(dot)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