Re: SQL Standards Compliance With Case

On Wednesday 12 July 2006 17:33, 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.

Likley, not ever ;)...

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

When I look below though...

>    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)
> Unable to execute your query. Please correct this error.
> You may need to update your database structure.

Something is wrong, that should work.. postgres will just fold the case..

postgres=# create table Foo (id bigserial primary key, Group_id integer);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for
table "foo"
CREATE TABLE
postgres=# CREATE INDEX Group_id ON Foo (Group_id);
CREATE INDEX

See it works :)

Something is wrong somewhere else... for example the below:

> ERROR: relation "role_id" already exists

AHA! I see what is happening... you can't have an index name the same as a
table name. Tell them just to change the index names... for example:

CREATE INDEX Group_id_idx ON Foo (Group_id);

Sincerely,

Joshua D. Drake


> I tried to execute:
> CREATE INDEX Role_id ON RolePermission (Role_id)
> Unable to execute your query. Please correct this error.
> You may need to update your database structure.
> ERROR: relation "company_id" already exists
> I tried to execute:
> CREATE INDEX company_id ON company_former_names (company_id)
> Unable to execute your query. Please correct this error.
> You may need to update your database structure.
> ERROR: relation "contact_id" already exists
> I tried to execute:
> CREATE INDEX contact_id ON contact_former_companies (contact_id)
> Unable to execute your query. Please correct this error.
> You may need to update your database structure.
> ERROR: relation "company_id" already exists
> I tried to execute:
> CREATE INDEX company_id ON contacts (company_id)
> Unable to execute your query. Please correct this error.
> You may need to update your database structure.
> ERROR: relation "province" already exists
> I tried to execute:
> CREATE INDEX province ON time_zones (province)
>
>    The first two appear to me to be case related, but I don't understand
> the last four at all.
>
>    I'll be quite appreciative of learning what the SQL standard has to say
> about case, where postgres differs, and what I should look for in the php
> scripts' SQL statements to resolve these errors.
>
> Rich

--
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/