Re: [HACKERS] Case Preservation disregarding case

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