Re: SQL Standards Compliance With Case

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:
> I'm trying to assist the XRMS developers port their application to
> postgres (8.1.x on), and it's almost there. One (perhaps the only) stumbling
> block is case for table and column (relation and attribute) names.
> Apparently MySQL allows for mixed case, while postgres wants only lower
> case. One of the development team asked me to enquire when postgres would be
> fully compliant with the SQL standard in this reqard.

It's probably worth pointing out here that the MySQL behavior they seem
to be expecting is considerably further from the spec than Postgres's
behavior. If I'm reading between the lines correctly, they are
expecting foo and Foo (both written without double-quotes) to be
distinct identifiers. But these are the same identifier per spec,
because the spec *requires* case-folding of unquoted identifiers.

The difference between what PG does and what the spec says can only be
observed when you sometimes double-quote a particular identifier and
sometimes don't. PG makes FOO, Foo, foo and "foo" the same, but
different from "FOO" or "Foo"; while the spec would have FOO, Foo, foo
and "FOO" the same but different from "foo" or "Foo".

The relevant bits from SQL92 section 5.2 are

10)The <identifier body> of a <regular identifier> is equivalent
to an <identifier body> in which every letter that is a lower-
case letter is replaced by the equivalent upper-case letter
or letters. This treatment includes determination of equiva-
lence, representation in the Information and Definition Schemas,
representation in the diagnostics area, and similar uses.

12)Two <regular identifier>s are equivalent if their <identifier
body>s, considered as the repetition of a <character string
literal> that specifies a <character set specification> of
SQL_TEXT, compare equally according to the comparison rules
in Subclause 8.2, "<comparison predicate>".

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

14)Two <delimited identifier>s are equivalent if their <delimited
identifier body>s (with all occurrences of <quote> replaced
by <quote symbol> and all occurrences of <doublequote symbol>
replaced by <doublequote>), considered as the repetition of a
<character string literal> that specifies a <character set spec-
ification> 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>".

regards, tom lane