Обсуждение: Database and table name case sensitivity

Hi all,

Just running into a few problems involving the case of table names.

I am writing a program that dynamically maps python objects into
postgres tables. These classes have mixed case names such as "BaseObject",
"AbcXyzObject" etc.

I call CREATE TABLE using the mixed case table name I want eg:

CREATE TABLE BaseObject (trivial int4);

From this I can do:

SELECT * from BaseObject - or
SELECT * from baseobject .

This is case insensitive here.

The problem I run into is that I want to later rebuild object from the
database dynamically and this requires querying the system tables.

SELECT relname from pg_class where relname not like 'pg%';

This returns the names in lower case only.

I am wondering if there is any reason that this case information is
discarded when creating tables?

Is there any neat ways around it that don't involve me having to maintain my own
table list?

If I patched postgres to store table names in a case sensitive way would
it be accepted, what problems would this introduce?

Thanks for any help you can provide.

Cheers,

Ben Leslie

(Should this goto the hackers list??)

On Thu, 08 Feb 2001 19:23:47 Ben Leslie wrote:
>Just running into a few problems involving the case of table names.
>
>I am writing a program that dynamically maps python objects into
>postgres tables. These classes have mixed case names such as "BaseObject",
>"AbcXyzObject" etc.
>
>I call CREATE TABLE using the mixed case table name I want eg:
>
>CREATE TABLE BaseObject (trivial int4);

If you need to preserve the case, you will have to use double quotes when
creating and querying:

CREATE TABLE "BaseObject" (trivial int4);
SELECT * from "BaseObject";

This also applies to attributes (column names).

Tony
--
Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/>
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26  C484 A42A 60DD 6C94 239D
Chat:  AOL/Yahoo: TonyG05    ICQ: 91183266
Linux. The choice of a GNU Generation. <http://www.linux.org/>

Quoting Ben Leslie <benno@sesgroup.net>:

> Hi all,
>
> Just running into a few problems involving the case of table names.
>
> I am writing a program that dynamically maps python objects into
> postgres tables. These classes have mixed case names such as
> "BaseObject",
> "AbcXyzObject" etc.

> I am wondering if there is any reason that this case information is
> discarded when creating tables?

I think it's part of one of the SQL specs. Most DB's either map to lower case,
some to upper case, some keep the case but ignore it.

This happens enough that when Sun did the original JDBC spec they included
methods to check what scheme the backend actually uses...

> Is there any neat ways around it that don't involve me having to
> maintain my own
> table list?

No. Quote the names, eg:

select * from "BaseObject";
create table "BaseObject" ( ... ) ;

>
> If I patched postgres to store table names in a case sensitive way would
> it be accepted, what problems would this introduce?

It would probably blow up every app currently in existence ;-)

Peter

--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

On Fri, 09 Feb 2001, Peter T Mount wrote:

> Quoting Ben Leslie <benno@sesgroup.net>:
>
> > Hi all,
> >

<snip>

>
> No. Quote the names, eg:
>
> select * from "BaseObject";
> create table "BaseObject" ( ... ) ;
>

Thanks a lot this works perfectly.

Cheers,

Benno