Repost: Get table/field-identifiers in uppercase
Hi all,
because no one noticed my last question, please forgive me this repost, but I
promised my boss to get an answer to this questions.
Here they are:
When a new table or field is created without quotes, it is assumed to be
case-insensitive. Herefore I have some questions:
- Is it SQL-92-conform to handle >"test"< like >test< without quotes, or
shouldn't it be >test< forced to lowercase?
- Oracle returns this no_matter_what-case_it_is-fields with
uppercase-letters. Is it possible for Postgresql, to imitate this behaviour?
- How is the handling of case-sensitivity handled in the system-catalogs? Is
ther any flag or depends it on the name of the object only?
Thank you very much in advance!
Klaus
--
Visit WWWdb at
http://wwwdb.org
When a new table or field is created without quotes, it is assumed to be
case-insensitive. Herefore I have some questions:- Is it SQL-92-conform to handle >"test"< like >test< without quotes, or
shouldn't it be >test< forced to lowercase?
I do not understand this question. If you want case sensitivity, you need
to quote your identifiers. Unquoted identifiers are case insensitive.
I do not think the standard states what should happen when you start mixing
quoted and unquoted identifiers for the same object.
- Oracle returns this no_matter_what-case_it_is-fields with
uppercase-letters. Is it possible for Postgresql, to imitate this behaviour?
No. PostgreSQL stores them in all lower case (Informix also).
- How is the handling of case-sensitivity handled in the system-catalogs? Is
ther any flag or depends it on the name of the object only?
The unquoted identifier is converted to all lower case, no flag.
The quoted identifier is taken as is.
Andreas
Import Notes
Resolved by subject fallback
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
I do not think the standard states what should happen when you start mixing
quoted and unquoted identifiers for the same object.
Actually, it does:
13)A <regular identifier> and a <delimited identifier> are equiva-
lent if the <identifier body> of the <regular identifier> (with
every letter that is a lower-case letter replaced by the equiva-
lent upper-case letter or letters) and the <delimited identifier
body> of the <delimited identifier> (with all occurrences of
<quote> replaced by <quote symbol> and all occurrences of <dou-
blequote symbol> replaced by <double quote>), considered as
the repetition of a <character string literal> that specifies a
<character set specification> of SQL_TEXT and an implementation-
defined collation that is sensitive to case, compare equally
according to the comparison rules in Subclause 8.2, "<comparison
predicate>".
The spec expects unquoted identifiers to be made case-insensitive by
folding them to upper case. We do it by folding to lower case, instead.
While this isn't 100% standard, it's unlikely to be changed. Too many
applications would break...
regards, tom lane
On Tuesday, 10. July 2001 11:45, you wrote:
When a new table or field is created without quotes, it is assumed to be
case-insensitive. Herefore I have some questions:- Is it SQL-92-conform to handle >"test"< like >test< without quotes, or
shouldn't it be >test< forced to lowercase?I do not understand this question. If you want case sensitivity, you need
to quote your identifiers. Unquoted identifiers are case insensitive.
I do not think the standard states what should happen when you start mixing
quoted and unquoted identifiers for the same object.
OK, lets assume I want the field test explicitly in lowercase (and ONLY in
lowercase). I can see no way to make this with the current implementation.
By the way in Oracle, we have the same problem, but everything is in
uppercase.
Regards & thanks
Klaus
--
Visit WWWdb at
http://wwwdb.org