From: Ken Johanson <pg-user@kensystem.com>
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case
Date: Sat, 02 Dec 2006 21:57:07 -0700
Message-ID: <45725923.7010500@kensystem.com> (raw)
In-Reply-To: <20061202201822.GD26365@svana.org>
References: <200610301928.51255.beauh@bluefrogmobile.com>
<23177.1162265010@sss.pgh.pa.us>
<EB48EBF3B239E948AC1E3F3780CF8F88012F88DD@MI8NYCMAIL02.Mi8.com>
<45712E31.1030004@kensystem.com>
<20061202133556.GC26365@svana.org>
<457199E4.3010401@kensystem.com>
<4571A3AF.6070608@zigo.dhs.org>
<4571AE66.7050304@kensystem.com>
<4571B392.6020303@zigo.dhs.org>
<4571C133.1060200@kensystem.com>
<20061202201822.GD26365@svana.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pg-user@kensystem.com, kleptog@svana.org
Subject: Re: [HACKERS] Case Preservation disregarding case
In-Reply-To: <45725923.7010500@kensystem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox