From: Gaetano Mendola <mendola@bigfoot.com>
To: A Bruce <abruce@localhost.com>
Subject: Re: constraitnt on case sensetive and case insensetive columns
Date: Mon, 26 Jul 2004 01:41:05 +0200
Message-ID: <41044511.20303@bigfoot.com> (raw)
In-Reply-To: <pan.2004.07.20.03.49.10.517277@monkeyhost>
References: <pan.2004.07.20.03.49.10.517277@monkeyhost>
-----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/dLQCgsy9t
IZrziKueFyht39zm+/XoD8w=
=gA20
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mendola@bigfoot.com, abruce@localhost.com
Subject: Re: constraitnt on case sensetive and case insensetive columns
In-Reply-To: <41044511.20303@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox