Thread: [PATCH] problem with DatabaseMetaData and mixed case table names

Hi pgsql developers,
today I found a problem using the latest version of the driver, from
CVS. This is an example that shows the problem:

---------------------------------------------------------
Connection c = ...;
Statement s = c.createStatement();

s.execute("create table MyTable ( PK numeric(10), primary key(pk))");

DatabaseMetaData md = c.getMetaData();
ResultSet rs = md.getPrimaryKeys(null,null, "MyTable")

while (rs.next())
    System.out.println("table: \"" + rs.getString("TABLE_NAME") +
    "\", column: \"" + rs.getString("COLUMN_NAME") + "\".");

rs.close();
s.close();
c.close();
---------------------------------------------------------

The resultset is empty, while it should contain a line for the
"PK" field.

If I lowercase the table name in «getPrimaryKeys» call than everything
works.
My question is: how do I know that postgresql changed my table name?

The solution I propose is, since it seems that postgresql always store
relation names in lowercase letters, to change the jdbc implementation
of all methods like «getPrimaryKeys» in order to pass lowercase relation
names to the backend.

The proposed patch change «escapeQuotes» method that is used for table
names, column names, schema names, type names, table name patters,
column name patterns, schema name patters and type name patterns.

The changed method return a lowercase string, that is thereafter used
in sql statements with '=' and 'like' operators.

Is this the right way to solve the problem?

Thanks,
Giuseppe

On Mon, 8 Aug 2005, Giuseppe Sacco wrote:

> Connection c = ...;
> Statement s = c.createStatement();
>
> s.execute("create table MyTable ( PK numeric(10), primary key(pk))");
>
> DatabaseMetaData md = c.getMetaData();
> ResultSet rs = md.getPrimaryKeys(null,null, "MyTable")
>
> while (rs.next())
>     System.out.println("table: \"" + rs.getString("TABLE_NAME") +
>     "\", column: \"" + rs.getString("COLUMN_NAME") + "\".");
>
> rs.close();
> s.close();
> c.close();
> ---------------------------------------------------------
>
> The resultset is empty, while it should contain a line for the
> "PK" field.
>
> If I lowercase the table name in �getPrimaryKeys� call than everything
> works.
> My question is: how do I know that postgresql changed my table name?
>
> The solution I propose is, since it seems that postgresql always store
> relation names in lowercase letters, to change the jdbc implementation
> of all methods like �getPrimaryKeys� in order to pass lowercase relation
> names to the backend.

Postgresql folds identifiers to lowercase as long as they are created
without quotes.  Using CREATE TABLE "MyTable" (...) will indeed retain the
case of MyTable.  We do not fold DatabaseMetaData parameters to lowercase
because that would prevent you from retrieving data from a table created
as "MyTable".  The DatabaseMetaData methods like
storesLowerCaseIdentifiers() helps allow portable code to call the other
methods with the correct name.  It doesn't help you if you don't know if
the table name was originally created as MyTable or "MyTable", but if
that's the case a call to getTables may be in order.

Kris Jurka

Giuseppe Sacco wrote:

> The solution I propose is, since it seems that postgresql always store
> relation names in lowercase letters, to change the jdbc implementation
> of all methods like «getPrimaryKeys» in order to pass lowercase relation
> names to the backend.

This assumption is wrong. PostgreSQL stores mixed case identifiers, but
if you do not quote them then they are forced to lowercase during
parsing. If you want to preserve case, quote your identifiers. See
http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

> Is this the right way to solve the problem?

No. It's valid to have tables called "foo", "Foo", and "FOO" (possibly
simultaneously); your patch would destroy the ability to examine
anything but "foo".

-O

Il giorno lun, 08/08/2005 alle 16.55 -0500, Kris Jurka ha scritto:
[...]
> Postgresql folds identifiers to lowercase as long as they are created
> without quotes.  Using CREATE TABLE "MyTable" (...) will indeed retain the
> case of MyTable.  We do not fold DatabaseMetaData parameters to lowercase
> because that would prevent you from retrieving data from a table created
> as "MyTable".  The DatabaseMetaData methods like
> storesLowerCaseIdentifiers() helps allow portable code to call the other
> methods with the correct name.  It doesn't help you if you don't know if
> the table name was originally created as MyTable or "MyTable", but if
> that's the case a call to getTables may be in order.
>
> Kris Jurka

Thank you, and to Oliver Jowett, for this explaination.

Giuseppe