Skip site navigation (1) Skip section navigation (2)

Re: constraitnt on case sensetive and case insensetive columns

From: Gaetano Mendola <mendola(at)bigfoot(dot)com>
To: A Bruce <abruce(at)localhost(dot)com>
Subject: Re: constraitnt on case sensetive and case insensetive columns
Date: 2004-07-25 23:41:05
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-sql
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 );
regression=# create unique index test_idx on test ( upper(a), upper(b), c);

Gaetano Mendola

Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird -


pgsql-sql by date

Next:From: Gaetano MendolaDate: 2004-07-25 23:45:02
Subject: Re: Please help - performance problems
Previous:From: Gaetano MendolaDate: 2004-07-25 23:35:16
Subject: Re: Object Create Date

pgsql-general by date

Next:From: Geoff CaplanDate: 2004-07-26 00:11:47
Subject: Re: Sql injection attacks
Previous:From: Bill MoranDate: 2004-07-25 23:17:12
Subject: Re: Sql injection attacks

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group