Re: Case Preservation disregarding case sensitivity?

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