Re: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity woes
On 1/28/06, Matthew Hixson [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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
Re: [GENERAL] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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
Re: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity woes
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity woes
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@ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity woes
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
