Thread: UNIQUE constraints on function results

Hi,

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? Thanks for clarification.

Regards

Markus


[1]: CREATE INDEX test_idx ON test(t1, lower(t2));

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

Emanuele Rocca wrote:
> you'll get a duplicate key error.

Thank you, that solves my problem.

Although it makes me wonder even more why I'm not allowed to define such
a constraint. Looks like all the necessary backend code is there.

Regards

Markus

Markus Schiltknecht <markus@bluegap.ch> writes:
>     UNIQUE (t1, lower(t2)));

> I can easily create an index for my needs [1], why can I not add such a
> unique constraint? Thanks for clarification.

Because the SQL spec defines this syntax, and it only allows column
names there.

Extending the spec in this particular direction is not as easy as it
might look, either.  What will you do with the information_schema
description of the unique constraint?

            regards, tom lane