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@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx]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
