Skip site navigation (1) Skip section navigation (2)

Re: [PATCH] problem with DatabaseMetaData and mixed case

From: Kris Jurka <books(at)ejurka(dot)com>
To: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [PATCH] problem with DatabaseMetaData and mixed case
Date: 2005-08-08 21:55:30
Message-ID: Pine.BSO.4.56.0508081646440.22483@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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


In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2005-08-08 21:58:25
Subject: Re: get question mark with Chinese/Japanese character ?
Previous:From: Giuseppe SaccoDate: 2005-08-08 16:20:43
Subject: [PATCH] problem with DatabaseMetaData and mixed case table names

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group