Re: Postgres 8.1.2, Java, JDO, and case sensitivity woes

The problem is that the JDO layer is looking for the table name in a
system table like so:

SELECT
n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attl
en,a.attnum,def.adsrc,dsc.description  FROM pg_catalog.pg_namespace
n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN
pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  LEFT JOIN
pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum =
def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON
(c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN
pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON
(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum
 > 0 AND NOT a.attisdropped  AND c.relname LIKE 'FOO'  AND a.attname
LIKE '%'  ORDER BY nspname,relname,attnum

That query returns nothing.  Changing FOO to foo returns the 4 rows
that the JDO layer is looking for.

So, on second thought its not a problem with case insensitive table
names, its a problem with the JDO layer looking for uppercase table
names listed in the system tables even though Postgres table names
are lowercase by default.  Completely different problems.

I renamed table foo to FOO, which makes the above query return
correctly, and now I see this in the log, later in the initialization:

LOG:  statement: PREPARE <unnamed> AS SELECT ns.helper AS ns_helper,
ns.name AS ns_name, ns.foo_id AS ns_foo_id, ns.title AS ns_title FROM
foo ns
ERROR:  relation "foo" does not exist

So, it would seem that table names are case insensitive in select
statements, but case sensitive in prepare statements.
   Can someone confirm or refute that?
   -M@


On Jan 27, 2006, at 3:59 PM, Roger Hand wrote:

> testdb=# CREATE TABLE foo (field_one int4);
> CREATE TABLE
>
> testdb=# INSERT INTO foo VALUES (1);
> INSERT 0 1
> testdb=# INSERT INTO foo VALUES (2);
> INSERT 0 1
>
> testdb=# SELECT * FROM foo;
>  field_one
> -----------
>          1
>          2
> (2 rows)
>
> testdb=# SELECT * FROM FOO;
>  field_one
> -----------
>          1
>          2
> (2 rows)
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Matthew Hixson
> Sent: Friday, January 27, 2006 3:53 PM
> To: Postgres General
> Subject: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity
> woes
>
>
> I'm trying to get a large Java application which makes use of an
> Oracle JDO layer to work with Postgres.  Set aside for a moment the
> discussion of whether or not that is going to work.
>    What I have found is that different parts of this application are
> referring to a table in all uppercase and in other parts referring to
> the table all in lowercase.  Is there a way to configure Postgres so
> that it does not treat "FOO" and "foo" as two different tables?
>    Thanks,
>     -M@
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>