Re: [HACKERS] Case Preservation disregarding case - Mailing list pgsql-sql

Martijn van Oosterhout wrote:
> On Sat, Dec 02, 2006 at 11:08:51AM -0700, Ken Johanson wrote:
>>> And my vote is to not have such an option. But I'm not the one who 
>>> decide so don't worry about what I think :-) I would like to have an 
>>> option to upper case the identifiers instead of lower casing them as pg 
>>> do. The sql standard say that they should be upper cased. But as far as 
>>> I know there are no plan at the moment to add such an option either. 
>>> Some time in the future I expect it to be implemented only because it's 
>>> the standard.
> 
> I think it's unlikely to happen anytime soon. The primary reason being
> that then you can no longer use indexes to search the catalog. Which

I'm pretty sure this is no the case - other DBs do allow index search on 
columns/identifiers regardless of their case. Probably the typical 
strategy is to use a case-insensitive hashtable (fold case for the keys 
before generating the hash). If its the actual data that you're 
referring to in index searches, that would be a separate topic I think.

> means it has to be fixed at initdb time. And it would break a large
> number of client apps, for no particularly good reason.

I take a different opinion on this:

-*If* the option to turn on case-insenetive behavior were selectable at 
the DB or session level, the existing apps could continue to use the 
case sensitve mode and be completely unaffected.

-IMO turning it on *globally* would only break apps that are built 
case-sensitivly *and* refer to identifiers of the same name (but mixed 
case) *and* are written for PG (since PG *had* been by and large 
non-portable until recently.. the addition of standard string quoting 
for example)

-It would *enhance* people's ability to "bring in" apps from so many 
other DBs which don't treat identifiers as case sensitive. More of a 
compatibility boon than loss. Thats is a particularly good reason to me 
(since I'm the one who has to issue DDL on all my camelCase columns and 
recode my identifiers).

> 
> Since the way identifiers are treated is user-visible, it would mean
> that apps would have to be coded to work with any setting. What would
> probably happen is that app A would only work with case-sensetive, and
> app B would only work with case-insensetive, and you end up with two
> apps that can't work on the same database.
> 
> That's *bad*, we don't want to go there.

That is a good point and I'd normally agree - entice people to use the 
lowest common denominator behavior and code their apps case-sensitive. 
And yet, the DBs that expect case-sens are now the minority, and we have:

a) programmers who code against MySQL or MSSQL, or;
b) are laymen try to run or port an app designed on MySQL to PG

Maybe not right per se - but the more popular way of doing things 
eventually wins out.

..

> 
>> In one way I think that even allowing creation of a separate "rowid" and 
>> "rowId" sort of violates set theory in a 4+ GL language... a "name" in 
>> its most abstract (human) sense doesn't (shouldn't) consider the case of 
>> its characters. Only what the characters are. A rowid is also a rowId 
>> (or ROWID). Who really intentionally mixes them? (only 3-4GL 
>> *programmers* who consider all-caps to represent constants in my 
>> experience).
> 
> The thing is, postgresql *is* case-insensetive, as is the whole SQL
> language. It not case-preserving, that's all. 

Right, it's case insensitive only if you're willing to accept case 
folding  (down) everything that's not quoted. Not being case-preserving, 
as you say.

But thats a pita to anyone coming from those "other" DBs and wants their 
column names to have mixed/camel case (for readability). PG right now 
*forces* them to change/adhere to an underscore naming, or to quote 
*every* mixed case identifier. You MUST tolerate having your column 
names stored in all-lower case, or else you must quote all of them.

Best,
Ken