Case Preservation disregarding case sensitivity?

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 null
userprofiletypeid | 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 null
userProfileTypeId | 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.