Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Case Preservation disregarding case sensitivity?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chuck McDevitt" <cmcdevitt(at)greenplum(dot)com>
Cc: "beau hargis" <beauh(at)bluefrogmobile(dot)com>, pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Case Preservation disregarding case sensitivity?
Date: 2006-10-31 06:34:56
Message-ID: 24861.1162276496@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
"Chuck McDevitt" <cmcdevitt(at)greenplum(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Teodor SigaevDate: 2006-10-31 07:55:00
Subject: Re: Index greater than 8k
Previous:From: Chuck McDevittDate: 2006-10-31 06:08:31
Subject: Re: [HACKERS] Case Preservation disregarding case

pgsql-sql by date

Next:From: roopa perumalrajaDate: 2006-10-31 06:54:18
Subject: Re: Add calculated fields from one table to other table
Previous:From: Chuck McDevittDate: 2006-10-31 06:08:31
Subject: Re: [HACKERS] Case Preservation disregarding case

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group