DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=SpeLAeU7dUzdQXsjJcRWRNzWm2q0/Nyulx6wWVh4lYerPQ1Pal38cAy/5VOTT2nsHrCcXi/dcd5mWKUgu91dfddAdX8dc9XDzd9Gf/xO9Gh9SjiWDVMo5zffjwArqhlXZnNSH0NLZ81qoYziyWaQyFXT6sy1wQe9b/9exnwAD2A=
	;
Date: Sat, 28 Oct 2006 07:38:59 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Case Preservation disregarding case sensitivity?
To: beau hargis <beauh@bluefrogmobile.com>, pgsql-sql@postgresql.org
In-Reply-To: <200610271623.37525.beauh@bluefrogmobile.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <467277.20359.qm@web31812.mail.mud.yahoo.com>

> Case was preserved. Now lets add the foreign key just as we did before (note 
> that the case in the table definition and the ALTER TABLE query is the same):
> 
> ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype  FOREIGN KEY 
> (userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
>  ^^^^^^^^^^^^^^^^^                                ^^^^^^^^^^^^^^^^^
> ERROR:  column "userprofiletypeid" referenced in foreign key constraint does 
> not exist

When ever you defince a column with quotes, all references to it must also contain quotes. Try:
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype  FOREIGN KEY 
("userProfileTypeId") REFERENCES user_profile_type ("userProfileTypeId");


> OK, another query (perfectly valid SQL):
> 
> insert into user_profile_type 
> (userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
>  ^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ 
> ERROR: column "userprofiletypeid" of relation "user_profile_type" does not 
> exist

Try:
insert into user_profile_type 
("userProfileTypeId","userProfileType") VALUES(1,'ABNORMAL');


> 
> I am hoping that there is an easy way to obtain case-preservation with 
> case-insensitivity, or at the very least, case-preservation and complete 
> case-sensitivity, or case-preservation and a consistant case-conversion 
> strategy. 
> 
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give me 
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not 
> both as I am seeing.

Perhaps in your queries or views you use the AS keywork to respecify the column name with
upper/lower cases.

i.e.
mydb=# select id as "Id" from foo;
   Id
---------
 goodbye
(1 row)


Regards,

Richard Broersma Jr.