Re: Bringing PostgreSQL torwards the standard regarding

Jan Wieck wrote:

> This is the reason why the setting has to be at least per database and
> cannot be changed after DB creation.

I think there's overwhelming consensus that db creation time is the
latest you can specify the canonical name setting for it. There's
probably a good case to be made for it to be when you initdb, so that it
is set as expected for shared tables.

(Is anyone actually doing anything on this?)

cheers

andrew

> What has to change is the behaviour of the name type operators, which
> will automatically change the uniqueness behaviour of the catalog
> indexes.
>
> In an UPPERCASE database
>
> foo/Foo/FOO false = FOO true
>
> In a lowercase database
>
> foo/Foo/FOO false = foo true
>
> In both of them
>
> foo/Foo/FOO false <> Foo true
> foo/Foo/FOO false = foo/Foo/FOO false
>
>
> Jan
>
>
> Dennis Bjorklund wrote:
>
>> On Sun, 25 Apr 2004, Andrew Dunstan wrote:
>>
>>> >> Why do you want two names? Just keep the original casing, and a
>>> boolean
>>> >> saying if it's quoted or not.
>>>
>>> Sorry - brain malfunction - yes, original casing plus boolean would
>>> work. In effect you could derive the canonical form from those two.
>>
>>
>> Say that you have this in the table with the identifier
>>
>> name quoted
>> ---- ------
>> Foo False
>>
>> Now you want to add the name "FOO"
>>
>> FOO True
>>
>> should you be allowed or is it a clash with the above?
>>
>> What if you also add "foo"
>>
>> foo True
>>
>> One of these two should be forbidden. And what about a quoted "FOO":
>>
>> FOO False
>> FOO True
>>
>> This case says it is not enough with an expressional unique index on
>> (upper(name), quoted). It would be easier to enforce uniqueness if one
>> store both the converted name and the original name:
>>
>> name orig_name
>> ---- ---------
>> FOO NULL <-- quoted one
>> FOO FOO <-- unquoted one
>>
>> and the first case
>>
>> FOO Foo <-- unquoted
>> FOO NULL <-- clashes with the first, good
>> foo NULL <-- no clash, works fine
>>
>> With this one can always use upper case translation as per sql spec and
>> psql can optionally show all unquoted identifiers as upper, lower or
>> mixed
>> case.
>>
>> Then we also have the INFORMATION_SCHEMA that should show the names in
>> UPPER CASE when not quoted, this since applications that are written for
>> the standard might depend on that (probably no application do today
>> but it
>> would be a valid case of use of the information schema).
>>
>
>