Re: Bringing PostgreSQL torwards the standard regarding

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).

--
/Dennis Björklund