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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)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
>