Thread: 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@
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: > 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 >
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