Message-ID: <455B5537.3060905@dunslane.net>
Date: Wed, 15 Nov 2006 12:58:15 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.6) Gecko/20060804 Fedora/1.0.4-0.5.1.fc5 SeaMonkey/1.0.4
MIME-Version: 1.0
To: Jim Nasby <jim@nasby.net>
CC: Simon Riggs <simon@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Chuck McDevitt <cmcdevitt@greenplum.com>,
	Stephan Szabo <sszabo@megazone.bigpanda.com>,
	beau hargis <beauh@bluefrogmobile.com>, pgsql-sql@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: [SQL] 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>
	<EB48EBF3B239E948AC1E3F3780CF8F88012F88DD@MI8NYCMAIL02.Mi8.com>
	<24861.1162276496@sss.pgh.pa.us>
	<EB48EBF3B239E948AC1E3F3780CF8F88012F89F8@MI8NYCMAIL02.Mi8.com>
	<20061031101314.R83230@megazone.bigpanda.com>
	<EB48EBF3B239E948AC1E3F3780CF8F88012F8A53@MI8NYCMAIL02.Mi8.com>
	<14059.1162363105@sss.pgh.pa.us>
	<EB48EBF3B239E948AC1E3F3780CF8F88012F8CD6@MI8NYCMAIL02.Mi8.com>
	<1162463264.3587.281.camel@silverbirch.site>
	<16897.1162482685@sss.pgh.pa.us>
	<1163540553.27956.109.camel@silverbirch.site>
	<E019F6B3-0E16-4FE8-9787-14250841C3D1@nasby.net>
In-Reply-To: <E019F6B3-0E16-4FE8-9787-14250841C3D1@nasby.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jim Nasby wrote:
> On Nov 14, 2006, at 2:42 PM, Simon Riggs wrote:
>> On Thu, 2006-11-02 at 10:51 -0500, Tom Lane wrote:
>>> "Simon Riggs" <simon@2ndquadrant.com> writes:
>>>> We have namespaces to differentiate between two sources of object 
>>>> names,
>>>> so anybody who creates a schema where MyColumn is not the same 
>>>> thing as
>>>> myColumn is not following sensible rules for conceptual distance.
>>>
>>> I'd agree that that is not a good design practice, but the fact remains
>>> that they *are* different per spec.
>>>
>>>> Would be better to make this behaviour a userset
>>>> switchable between the exactly compliant and the more intuitive.
>>>
>>> That's certainly not happening --- if you make any changes in the
>>> semantics of equality of type name, it would have to be frozen no
>>> later than initdb time, for exactly the same reasons we freeze
>>> locale then (hint: index ordering).
>>
>> [Re-read all of this after Bruce's post got me thinking.]
>>
>> My summary of the thread, with TODO items noted:
>>
>> 1. PostgreSQL doesn't follow the spec, but almost does, with regard to
>> comparison of unquoted and quoted identifiers. DB2 does this per spec.
>>
>> 2. TODO: We could follow the spec, but it would need an initdb option;
>> some non-SQL:2003 standard PostgreSQL programs would not work as they do
>> now. This is considered a minor, low priority item, though.
>>
>> 3. TODO: We could set column headers better if we wanted to (rather
>> than ?column? we could use e.g. Sum_ColumnName etc)
>
> Did the idea of preserving the original case and using that for output 
> column names, /d, etc. get shot down? I thought it would be a useful 
> addition...


I think there is broad agreement that we need to provide optional 
minimally spec compliant behaviour (fold unquoted to upper case, 
otherwise as now). I am not sure how invasive either the "case preserved 
+ case insensitive comparison" or the "case preserved + case sensitive 
comparison" option would be. I don't know that anything has been ruled 
out. Until someone produces a patch or a definite design and analysis we 
are a bit in the dark. Personally, I would like to see all of these as 
options ;-)

I think Simon's item 3 above is a separate issue.

cheers

andrew