Re: UNIQUE constraints on function results

Hello Markus,

* Markus Schiltknecht <markus@bluegap.ch>, [2006-10-05 11:16 +0200]:
>  I've been trying to add a unique constraint on a row and a function
>  result of a row. I.e.:
>
>  CREATE TABLE test (
>      id SERIAL PRIMARY KEY,
>      t1 TEXT NOT NULL,
>      t2 TEXT NOT NULL,
>      UNIQUE (t1, lower(t2)));
>
>  That fails with a syntax error (on 8.2beta1). While UNIQUE(t1, t2) works
>  like a charm, it's not exactly what I want.
>
>  I can easily create an index for my needs [1], why can I not add such a
>  unique constraint?

You can create a unique index.

CREATE UNIQUE INDEX idx_name ON test (t1, lower(t2));

INSERT INTO test (t1, t2) VALUES ('some text', 'Other Text');

Trying to insert the following row:

INSERT INTO test (t1, t2) VALUES ('some text', 'other text');

you'll get a duplicate key error.

ciao,
    ema