Case Preservation disregarding case sensitivity? - Mailing list pgsql-sql

Hello all.

As this is my first post to this particular mailing list, let me preface this 
with a couple of items:

1) I have, indeed, searched far and wide for the answer to my question. I have 
read and understood the responses to similar posts posing similar questions. 
My next stop after this is the postgresql source code. 

2) I develop database driven applications for telephony and mobile 
applications in C/C++, perl and python. The databases I am working with 
exceed 100GB. We have several production database systems.

3) I am pushing hard to replace our MySQL installations with PostgreSQL 
(open-source and/or commercial) because Postgres has performed better by 
several orders of magnitude and has been more stable and as consumed half the 
resources in my testing.


The behavior I am seeing in Postgres in my development and migration process 
is a showstopper. The manner in which Postgres handles case-sensitivity 
breaks the current tools that I use and the applications that have been 
written. 

I am aware of the double-quote 'feature' which indicates that an element 
should be treated in a case-sensitive way. This as been the 'answer' to every 
question of this sort. This 'feature' does not solve the problem and 
introduces other problems.

I have a data set that is designed in such a way as:

CREATE TABLE user_profile (  userProfileId int8 not null primary key,  userProfileTypeId int8 not null
);

CREATE TABLE user_profile_type (  userProfileTypeId int8 not null primary key,  userProfileType varchar not null
);
Ok, the description of the table from the database:
      Table "public.user_profile"     Column       |  Type  | Modifiers
-------------------+--------+-----------userprofileid     | bigint | not nulluserprofiletypeid | bigint | not null
Indexes:   "user_profile_pkey" PRIMARY KEY, btree (userprofileid)

Ok, adding a foreign key:

ALTER table user_profile ADD CONSTRAINT fk_uproftype  FOREIGN KEY 
(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);

...works successfully.

Works as it is supposed to work. Postgres is supposed to be case sensitive, 
which is not a problem, but I want it to preserve the case of the column 
names. 

Every search has turned up the suggestion of using double-quotes around 
identifiers in the creation of the tables. So we'll try that:

DROP TABLE user_profile;
DROP TABLE user_profile_type;
CREATE TABLE user_profile (  "userProfileId" int8 not null primary key,  "userProfileTypeId" int8 not null
);

CREATE TABLE user_profile_type (  "userProfileTypeId" int8 not null primary key,  "userProfileType" varchar not null
);

The table description:
     Table "public.user_profile"     Column       |  Type  | Modifiers
-------------------+--------+-----------userProfileId     | bigint | not nulluserProfileTypeId | bigint | not null
Indexes:   "user_profile_pkey" PRIMARY KEY, btree ("userProfileId")

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

The case was preserved, but the case-insensitivity only works one way. The 
case of the input identifier was 'normalized' in a way, but not matched 
against the column names in a case-insensitive way. This is the behavior that 
breaks the tools that I use. I cannot modify these tools to add quotes around 
the identifiers, and in my mind I should not have to.

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

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. 

The case of the column names need to be preserved because that is the way the 
schema is designed and most importantly (VERY, VERY IMPORTANT), column names 
are used in apps as hash values, or as named references which are case 
sensitive and as such need to be delivered to the client in exactly in the 
manner specified at the time of table creation.

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.

Thanks in advance. I am hoping to find a solution to this so I can actually 
convert one of our databases to use Postgres. And I can say that little 
issues like this are precisely why Postgres was never used in this 
organization before, even though several of the other database developers 
like the features, stability and performance of Postgres.