Postgres 8.1.2, Java, JDO, and case sensitivity woes

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

Matthew Hixson wrote on 28.01.2006 00:53:

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?

Simply don't use quotes, never. Then case will not matter.

Thomas

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:

On Jan 27, 2006, at 4:10 PM, Thomas Kellerer wrote:

Matthew Hixson wrote on 28.01.2006 00:53:

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?

Simply don't use quotes, never. Then case will not matter.

These queries are being put together by the JDO layer.
-M@

On Fri, 27 Jan 2006, Matthew Hixson wrote:

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

When using DatabaseMetaData calls to determine what tables and columns are
available you must be aware of what case the search terms need to be
passed in with. You must know if your tables were created with or
without quotes, but then you can use the DatabaseMetaData methods
stores[XXX]Case[Quoted]Idetifiers() to determine what case to pass
parameters to methods like getTables(). It's unclear what control (if
any) you have over this, but that's how it's supposed to work.

Kris Jurka

On 1/28/06, Matthew Hixson <hixson@poindextrose.org> wrote:

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?

http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

--
marko