Re: SQL Standards Compliance With Case

On Wed, 12 Jul 2006, Rich Shepard wrote:

> 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. So I'm asking. Not
> challenging, not complaining, but asking to learn something about case and
> the SQL standard as implemented in postgres.

AFAIK, SQL says that an non-quoted identifier such as Foo is treated as
FOO (case-folded to upper). PostgreSQL currently treats it as foo
(case-folded to lower). Quoted identifiers are not case-folded and are
compared case-sensitive.

So, for example my understanding of spec would say:

create table Foo(a numeric(10,3));
create table FoO(b numeric(11,4));
-- invalid because this is the same table name as the first create
create table "foo"(a numeric(12,5));
-- valid in SQL, invalid in PostgreSQL
create table "Foo"(a numeric(13,6));
-- valid, that's actually the mixedcase table Foo rather than FOO

or:
create table "Foo"(a numeric(10,3));
select * from Foo -- invalid, that's FOO not Foo
select * from "Foo" -- valid

create table Foo(a numeric(10,3)); -- folded to FOO
select * from foo -- valid
select * from "foo" -- invalid

>
> While I would prefer to not read the latest SQL standard specification,
> I'd like to help resolve the last six errors when I try to install XRMS on
> my postgres-8.1.4 system.
>
> Here's what the install.php script returns:
>
> Unable to execute your query. Please correct this error.
> You may need to update your database structure.
> ERROR: relation "group_id" already exists
> I tried to execute:
> CREATE INDEX Group_id ON GroupUser (Group_id)

These seem to be complaining that there's already a table, view, index,
etc with that name already. Is there one being created with a different
case that's assuming that it'll preserve case rather than fold?