[SQL] 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 perf
Re: [SQL] Case Preservation disregarding case sensitivity?
On Friday 27 October 2006 19:38, Joe wrote: > Hi Beau, > > On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote: > > 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. > > > > I went through the same issue in my conversion from MySQL to Postgres > and (since I had a small application) I ended up changing up all my > tables and columns "UserProfile" to user_profile. > > I'm afraid however, that it's MySQL that is the odd man out. I haven't > researched this completely but I believe PG follows either the FIPS-127 > or SQL-92 standard with respect to what are called "delimited > identifiers". Basically, this says if you want case sensitivity in > identifier names, you have to use double quotes wherever you refer to > the identifier. Without the double quotes, the SQL implementor can > either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as > PG does) when it displays those identifiers. > > Joe Again, I am at the same point I was at when searching and searching for information on the problem, and I am not sure what the SQL standard has to say about it: I do not, and I dont think that anyone else who is struggling to deal with the problem does, care about case-sensitivity. I am interested in case preservation of column names. I do, indeed, want identifiers treated in a case insensitive way, but I want the case PRESERVED in the table definitions and I want that case, as preserved, to be reflected in the field names as returned by the server to any client library that connects and initiates a query. Case-preservation is not the same as case-sensitivity; nor is case-normalization the same as case-insensitivity. What PostgreSQL is doing is converting any, and all, identifiers to a lower case and then matching those against the identifiers (as stored in the table definition) in a case-sensitive manner. It 'normalizes' the case of the identifiers so that it has a common internal representation; the desires of the programmer and database architect be damned. Referenced specification details: From FIPS-127: === 3. Delimited identifiers. In the previous ANSI SQL specification, it was not possible for an application to specify identifiers with spaces or other special symbols. Also, it was not possible to protect against future assaults on the name space for (identifier) by additions to the (reserved word) list. The new facility for (delimited identifier) allows a user to enclose all identifiers in double-quotation marks, thereby ensuring that the name defined or referenced may contain spaces or other special symbols and will not be impacted by future additions to the (reserved word) list. === From SQL-92/Sec. 5.2: === 10)The of a is equivalent to an in which every letter that is a lower- case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equiva- lence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. 11)The of a (with every letter that is a lower-case letter replaced by the equivalent upper-case letter or letters), treated as the repetition of a that specifies a of SQL_TEXT, shall not be equal, according to the comparison rules in Subclause 8.2, "", to any (with every letter that is a lower-case letter replaced by the equivalent upper-case letter or letters), treated as the repetition of a that specifies a of SQL_TEXT. 12)Two s are equivalent if their s, considered as the repetition of a that specifies a of SQL_TEXT, compare equally according to the comparison rules in Subclause 8.2, "". 13)A and a are equiva- lent if the of the (with every letter that is a lower-case letter replaced by the equiva- lent upper-case letter or letters) and the of the (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_TEXT and an implementation- defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". 14)Two s are equivalent if their s (with all occurrences
