Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 3EE439FB311;
	Tue, 31 Oct 2006 02:35:04 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 23690-02; Tue, 31 Oct 2006 06:35:01 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id A7CAE9FB257;
	Tue, 31 Oct 2006 02:35:00 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k9V6YumF024862;
	Tue, 31 Oct 2006 01:34:56 -0500 (EST)
To: "Chuck McDevitt" <cmcdevitt@greenplum.com>
cc: "beau hargis" <beauh@bluefrogmobile.com>, pgsql-sql@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case sensitivity? 
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>
Comments: In-reply-to "Chuck McDevitt" <cmcdevitt@greenplum.com>
	message dated "Tue, 31 Oct 2006 01:08:31 -0500"
Date: Tue, 31 Oct 2006 01:34:56 -0500
Message-ID: <24861.1162276496@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200610/244
X-Sequence-Number: 26633

"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