Re: SQL Standards Compliance With Case - Mailing list pgsql-general

Rich Shepard <rshepard@appl-ecosys.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