Re: Case Preservation disregarding case sensitivity?

[Richard Broersma Jr <rabroersma(at)yahoo(dot)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.