constraitnt on case sensetive and case insensetive columns

[A Bruce]

hello,

I am attempting to convert a oracle database to postgresql and I am having
some problems creating a constraint across multiple columns which are a
mixture of case insensitive and case sensitive.

The original oracle database created an index with:
CREATE UNIQUE INDEX hatidx ON hats (upper(name), upper(server), login);

However postgresql can handle multiple columns in the index, or one function,
but not multiple functions, thus this fails.

Queries are only done using the actual values, so the presence of the
index is not required for performance reasons, and exists only to
enforce the constraint that (upper(name), upper(server), login) is a
unique tuple. Is there anyway to create a constraint which will check
this? I suspect it would be possible to create a trigger to check this,
however this is a little ugly, and i would like something more similar to
to the original if possible.

Any suggestions as to how to approach this would be greatly appreciated,
-bruce

[Gaetano Mendola]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A Bruce wrote:

I'm using the 7.4.x version and what you ask for is supported:

regression=# create table test ( a varchar, b varchar, c varchar );
CREATE TABLE
regression=# create unique index test_idx on test ( upper(a), upper(b), c);
CREATE INDEX

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBBET87UpzwH2SGd4RAvUeAJ4vG0CxIQdUe8KjsYs/kk7yC1/dLQCgsy9t
IZrziKueFyht39zm+/XoD8w=
=gA20
-----END PGP SIGNATURE-----

[A Bruce]

> I'm using the 7.4.x version and what you ask for is supported:
>
> regression=# create table test ( a varchar, b varchar, c varchar );
> CREATE TABLE
> regression=# create unique index test_idx on test ( upper(a), upper(b), c);
> CREATE INDEX

Thank you for pointing this out, I am currently using 7.3 but I will
upgrade.

Thanks again!
-Bruce