SQL Standards Compliance With Case

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: SQL Standards Compliance With Case
Date: 2006-07-13 00:33:20
Message-ID: Pine.LNX.4.64.0607121722530.14352@salmo.appl-ecosys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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)
Unable to execute your query. Please correct this error.
You may need to update your database structure.
ERROR: relation "role_id" already exists
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

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc.(TM) | Accelerator
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2006-07-13 01:03:23 Re: SQL Standards Compliance With Case
Previous Message Ron Johnson 2006-07-12 21:58:11 Re: Fwd: Long term database archival