Thread: DatabaseMetaData.getTables() is silently quoting table identifiers?

Hi,

 If I create table MY_TABLE (unquoted uppercase), then my_table
(lowercase) is actually created. This is clearly documented, so fine.

<http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS>

But now if I use .getTables(null, null, "MY_TABLE", null) to ask
whether this table actually exists, the answer is "no" (empty) because
.getTables() acts like I quoted the MY_TABLE identifier.

It's quite annoying to create a table and not be able to see it...

Tested with  postgresql-server.i686 8.0.7-1PGDG and 8.2dev-503
Maybe this is a server-side issue?


Cheers,

Marc

This is because PostgreSQL allows the creation of case-sensitive table
names.

If you run:  CREATE TABLE MY_TABLE ...;

then the created table name is my_table.

But if you run:  CREATE TABLE "MY_TABLE" ...;

then the created table name is MY_TABLE.

So in order for PG to differentiate between my_table and MY_TABLE, the
getTables() function has to be case-sensitive.

-- Mark

On Thu, 2006-07-20 at 18:21 +0200, Marc Herbert wrote:
> Hi,
>
>  If I create table MY_TABLE (unquoted uppercase), then my_table
> (lowercase) is actually created. This is clearly documented, so fine.
>
> <http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS>
>
> But now if I use .getTables(null, null, "MY_TABLE", null) to ask
> whether this table actually exists, the answer is "no" (empty) because
> .getTables() acts like I quoted the MY_TABLE identifier.
>
> It's quite annoying to create a table and not be able to see it...
>
> Tested with  postgresql-server.i686 8.0.7-1PGDG and 8.2dev-503
> Maybe this is a server-side issue?
>
>
> Cheers,
>
> Marc
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

Marc Herbert <Marc.Herbert@continuent.com> writes:

> But now if I use .getTables(null, null, "MY_TABLE", null) to ask
> whether this table actually exists, the answer is "no" (empty) because
> .getTables() acts like I quoted the MY_TABLE identifier.
>
> It's quite annoying to create a table and not be able to see it...
>
> Tested with  postgresql-server.i686 8.0.7-1PGDG and 8.2dev-503
> Maybe this is a server-side issue?


I finally did my archeology homework:

Date: 2002-10-27 09:39:54 GMT
Subject: DatabaseMetaData.getTables() problem

<http://comments.gmane.org/gmane.comp.db.postgresql.jdbc/1614>