Re: [SQL] Case Preservation disregarding case - Mailing list pgsql-hackers
From | Chuck McDevitt |
---|---|
Subject | Re: [SQL] Case Preservation disregarding case |
Date | |
Msg-id | EB48EBF3B239E948AC1E3F3780CF8F88012F89F8@MI8NYCMAIL02.Mi8.com Whole thread Raw |
In response to | Re: [SQL] Case Preservation disregarding case sensitivity? (beau hargis <beauh@bluefrogmobile.com>) |
Responses |
Re: [SQL] Case Preservation disregarding case
Re: [SQL] Case Preservation disregarding case |
List | pgsql-hackers |
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] 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 setspecification> of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally accordingto 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, compareequally 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
pgsql-hackers by date: