There was some discussion a couple of years ago on the -hackers list
about it, so you might like to review the archives. The consensus seemed
to be that behaviour would need to be set no later than createdb time.
The options I thought of were:
. current postgres behaviour (we need to do this for legacy reasons, of
course, as well as to keep happy the legions who hate using upper case
for anything)
. strictly spec compliant (same as current behaviour, but folding to
upper case for unquoted identifiers rather than lower)
. fully case sensitive even for unquoted identifiers (not spec compliant
at all, but nevertheless possibly attractive especially for people
migrating from MS SQLServer, where it is an option, IIRC).
To this you propose, as I understand it, to have a fourth possibility
which would be spec compliant for comparison purposes but would label
result set columns with the case preserved name originally used (or
would you use the casing used in the query?).
These could be accomplished I think with a second catalog column like
you suggest, in a number of places, but making sure all the code paths
were covered might be somewhat laborious. We could probably add the
second option without being nearly so invasive, though, and some people
might feel that that would be sufficient.
cheers
andrew
Chuck McDevitt wrote:
We treated quoted identifiers as case-specific, as the spec requires.
In the catalog, we stored TWO columns... The column name with case
converted as appropriate (as PostgreSQL already does), used for looking
up the attribute,
And a second column, which was the column name with the case exactly as
entered by the user.
So, your example would work just fine.
-----Original Message-----
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Monday, October 30, 2006 10:35 PM
To: Chuck McDevitt
Cc: beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case
sensitivity?
"Chuck McDevitt" <[EMAIL PROTECTED]> writes:
At Teradata, we certainly interpreted the spec to allow
case-preserving,
but case-insensitive, identifiers.
Really?
As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2)
26) A <regular identifier> and a <delimited identifier> are
equivalent if the <identifier body> of the <regular
identifier>
(with every letter that is a lower-case letter replaced by
the
corresponding upper-case letter or letters) and the
<delimited
identifier body> of the <delimited identifier> (with all
occurrences of <quote> replaced by <quote symbol> and all
occurrences of <doublequote symbol> replaced by <double
quote>),
considered as the repetition of a <character string literal>
that specifies a <character set specification> of
SQL_IDENTIFIER
and an implementation-defined collation that is sensitive to
case, compare equally according to the comparison rules in
Subclause 8.2, "<comparison predicate>".
27) Two <delimited identifier>s are equivalent if their
<delimited
identifier body>s, considered as the repetition of a
<character
string literal> that specifies a <character set
specification>
of SQL_IDENTIFIER and an implementation-defined collation
that is sensitive to case, compare equally according to the
comparison rules in Subclause 8.2, "<comparison predicate>".
Note well the "sensitive to case" bits there. Now consider
CREATE TABLE tab (
"foobar" int,
"FooBar" timestamp,
"FOOBAR" varchar(3)
);
We can *not* reject this as containing duplicate column names, else we
have certainly violated rule 27. Now what will you do with
SELECT fooBar FROM tab;
? The spec is unquestionably on the side of "you selected the varchar
column"; historical Postgres practice is on the side of "you selected
the int column". AFAICS a case-insensitive approach would have to
fail with some "I can't identify which column you mean" error. I am
interested to see where you find support for that in the spec...
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
