Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 15E399FB39C;
	Tue, 31 Oct 2006 13:12:50 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 39427-02; Tue, 31 Oct 2006 13:12:28 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from exprod6og55.obsmtp.com (exprod6og55.obsmtp.com [64.18.1.191])
	by postgresql.org (Postfix) with SMTP id 0CD899FB350;
	Tue, 31 Oct 2006 13:11:52 -0400 (AST)
Received: from source ([63.240.6.44]) by exprod6ob55.postini.com
	([64.18.5.12]) with SMTP; Tue, 31 Oct 2006 09:11:50 PST
Received: from 172.16.1.148 by mail.mi8.com with ESMTP (- Welcome to Mi8
	Corporation www.Mi8.com (D4)); Tue, 31 Oct 2006 12:11:30 -0500
X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230
Received: from MI8NYCMAIL02.Mi8.com ([172.16.1.153]) by
	D01SMTP05.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 31 Oct
	2006 12:11:23 -0500
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Subject: Re: [HACKERS] Case Preservation disregarding case
Date: Tue, 31 Oct 2006 12:11:16 -0500
Message-ID: <EB48EBF3B239E948AC1E3F3780CF8F88012F89F8@MI8NYCMAIL02.Mi8.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [HACKERS] [SQL] Case Preservation disregarding case sensitivity?
thread-index: Acb8trFOVQK9VtklReyxjHXzYCJrdgAWHdeQ
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>
	<24861.1162276496@sss.pgh.pa.us>
From: "Chuck McDevitt" <cmcdevitt@greenplum.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
cc: "beau hargis" <beauh@bluefrogmobile.com>, pgsql-sql@postgresql.org,
	pgsql-hackers@postgresql.org
X-OriginalArrivalTime: 31 Oct 2006 17:11:23.0350 (UTC)
	FILETIME=[976D5760:01C6FD0F]
X-WSS-ID: 69595C322TS7541328-05-01
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/57
X-Sequence-Number: 26709

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:tgl@sss.pgh.pa.us]=20
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" <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