Re: [PATCH] problem with DatabaseMetaData and mixed case

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