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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
