Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D1C1B9FA2A6; Sat, 2 Dec 2006 03:53:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 44169-06; Sat, 2 Dec 2006 03:53:31 -0400 (AST) X-Greylist: delayed 00:11:50.414797 by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from www01.kensystem.com (www01.kensystem.com [72.19.170.120]) by postgresql.org (Postfix) with ESMTP id 9C4D09F9BEC; Sat, 2 Dec 2006 03:53:31 -0400 (AST) Received: from 192.168.0.3 by www01.kensystem.com (SMTPD); id s20061202074137.73438; Sat, 02 Dec 2006 07:41:37 Message-ID: <45712E31.1030004@kensystem.com> Date: Sat, 02 Dec 2006 00:41:37 -0700 From: Ken Johanson Reply-To: ken@kensystem.com User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 CC: Tom Lane , pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Case Preservation disregarding case References: <200610271623.37525.beauh@bluefrogmobile.com> <1162003132.687.8.camel@pampa> <200610301928.51255.beauh@bluefrogmobile.com> <23177.1162265010@sss.pgh.pa.us> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200612/24 X-Sequence-Number: 26980 Chuck McDevitt wrote: > At Teradata, we certainly interpreted the spec to allow case-preserving, > but case-insensitive, identifiers. > Users really liked it that way My 2 thoughts: 1: It seems like this behavior of case sensitive-or-not-identifiers could/should be a config option -- either globally for the server, database, or at the connection/session level. Other databases *do* support this type of granular config of misc SQL behavior -- its essential for shared hosting environments. Without it some users just *cant* make the switch. Quoting all an app's identifiers -- or renaming camel-case to underscored -- show stopper. 2: Even though the spec state different (that identifiers should be treated as case sensitive or else folded), precedence seems to have changed that: a) The databases that enforce this rule are fewer, I believe. IMO SQL is now considered even higher than a 4GL language because it use is so widespread - laymen need to use it. b) the fact that different identifiers of mixed case could even coexist in a table-columns or 'AS' or 'JOIN' -- really represents a more of an err'd design -- and a case-insen option would detect this (unlike the current behavior). It would throw an immediate ("fail fast") runtime exception. So I think it's *safer*. (If tbl.rowId and tbl.rowid both exist in a table or AS identifiers, something bad _will_ happen when someone takes over a project) If there were a new default behavior (or just config option added), my vote would, without a doubt, be for case-insens (yet case preserving) mode... even when using quoting identifiers. This case sen. behavior doesn't seem to offer any advantage/safety. ken