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?
