From: Aaron Bono <postgresql@aranya.com>
To: Ken Johanson <pg-user@kensystem.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Martijn van Oosterhout <kleptog@svana.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case
Date: Fri, 8 Dec 2006 08:09:10 -0600
Message-ID: <bf05e51c0612080609y2201db8cva5ec3e99391fcceb@mail.gmail.com> (raw)
In-Reply-To: <457646FA.1020804@kensystem.com>
References: <200612031657.kB3Gv6408322@momjian.us>
<457646FA.1020804@kensystem.com>
On 12/5/06, Ken Johanson <pg-user@kensystem.com> wrote:
>
> 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)
>
Wasn't the whole reason this came up because someone wanted to do SELECT *
FROM some_table and have the returned columns returned in a case that was
viewable? This thread has gone on for so long I don't remember for sure and
I have not kept up with the thread very well.
If this is the "case", no pun intended, then why not create views like this:
CREATE VIEW my_view (
"Person's ID",
"Person's Name"
) AS
SELECT
person_id,
first_name || ' ' || last_name
FROM person
;
When I do a SELECT * FROM my_view, I get "Person's ID", not "PERSON'S ID" or
"person's id".
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, pg-user@kensystem.com, bruce@momjian.us, tgl@sss.pgh.pa.us, kleptog@svana.org
Subject: Re: [HACKERS] Case Preservation disregarding case
In-Reply-To: <bf05e51c0612080609y2201db8cva5ec3e99391fcceb@mail.gmail.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