Re: [HACKERS] Case Preservation disregarding case

Bruce Momjian wrote:
> Tom Lane wrote:
>>
>> The real bottom line, though, is that this community has little respect
>> for proposals that involve moving away from the SQL spec rather than
>> closer to it; and that's what you're asking us to do.  The spec is not
>> at all vague about the case-sensitivity of identifiers.  Now certainly
>> we are not doing exactly what the spec says, but what you ask is even
>> less like the spec's requirements.
>
> I think there are two major issues here:
>
>       o  if you quote identifiers that have any upper-case characters,
>          do it both at table creation and use
>       o  display of non-quoted identifiers is lower-case
>
> I think we are OK making people either always quote, or always not
> quote.  What we don't currently have a good answer for is people wanting
> the identifiers displayed using the original case.  You can use quotes
> all the time of you want such display, but it is a pain.  I think this
> is the crux of the complaints.
>
> Saving the case of the original creation and displaying that does work,
> but then it isn't clear if the identifier needs quotes (is the
> upper-case real or just for display).  This gets us into even more
> confusion.
>
> Can someone can think of an answer to all this?
>

I believe there is no one answer that will solve all cases.. but one
solution that might come close is something like this (I take no credit,
others have described this before me):

PG would support two modes of operation:

1) Standard compliant, and;

2) Quirks / compatibility mode (case preserving but case-insensitive)

I believe (and may be wrong) that the #2 mode-of-operation would only
require:

a) resultset data to have it's (unquoted) identifiers to be case-folded,
and;
b) queries with (unquoted) identifiers (joins / aliases etc) would
continue in the planner to be case folded, so would work as now (and in
effect be case-insensitive).
c) the table describe syntax would return the case-preserved id name
(which it already does if quoted?), or d:
d) in addition to a), optionally send metadata describing the
case-preserved name; this might someday allow newer drivers to display
(display only, not column lookup) those names in database-designer views
(iSQL types apps)

If #a is possible, then drivers should not break, even if the DB is
config'd to use setting #2. But I don't know the low-level protocol of
PG to know if that is possible. ..

Hopefully I'm not missing any points here, please correct me if so...

I believe what I described above (while not being standard complaint per
se) is identical to how mysql and mssql work (operationally anyway)
right now.. On the other had Oracle and others work as PG does now, but
my point in discussing this, is that the first two DBs have enough
market, that offering a compatibility mode to ease the burden of porting
apps would have substantial value (I know this from experience)