From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chuck McDevitt <cmcdevitt@greenplum.com>
Cc: beau hargis <beauh@bluefrogmobile.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case sensitivity?
Date: Tue, 31 Oct 2006 01:34:56 -0500
Message-ID: <24861.1162276496@sss.pgh.pa.us> (raw)
In-Reply-To: <EB48EBF3B239E948AC1E3F3780CF8F88012F88DD@MI8NYCMAIL02.Mi8.com>
References: <200610271623.37525.beauh@bluefrogmobile.com>
<1162003132.687.8.camel@pampa>
<200610301928.51255.beauh@bluefrogmobile.com>
<23177.1162265010@sss.pgh.pa.us>
<EB48EBF3B239E948AC1E3F3780CF8F88012F88DD@MI8NYCMAIL02.Mi8.com>
"Chuck McDevitt" <cmcdevitt@greenplum.com> 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...
regards, tom lane
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: tgl@sss.pgh.pa.us, cmcdevitt@greenplum.com, beauh@bluefrogmobile.com, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case sensitivity?
In-Reply-To: <24861.1162276496@sss.pgh.pa.us>
* 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