constraitnt on case sensetive and case insensetive columns

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

Re: constraitnt on case sensetive and case insensetive columns

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

A Bruce wrote:

| 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
|

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/dLQCg sy9t
IZrziKueFyht39zm+/XoD8w=
=gA20
-----END PGP SIGNATURE-----

Re: constraitnt on case sensetive and case insensetive columns

> 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