0

I connect to a PostgreSQL server with: jdbc:postgresql://127.0.0.1/mydb?currentSchema=app96. I need to list tables that are present and create those that are missing during the initialization of my server.

Here's my code:

final ResultSet rs2 =
    conn.getMetaData().getTables(null, "", null, new String[] { "TABLE" });
while (rs2.next()) {
    System.out
        .println(rs2.getString("TABLE_SCHEM") + "." + rs2.getString("TABLE_NAME"));
}

It prints:

app96.t1
app96.t2
public.administration$account
public.appmodule$uploadedfile
public.audittrail$audittrailsuperclass
...

From javadoc of getTables:

schemaPattern ... "" retrieves those without a schema

But it seems that getTables treats empty string the same way as null. Is there a pure JDBC way to filter by current schema or do I have to implement DB-specific filters myself?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
basin
  • 3,949
  • 2
  • 27
  • 63
  • 1
    Well there is no such thing as a table without a schema in Postgres. The need to fully qualify a table reference in a query depends on the schemas specified in `search_path` –  Nov 17 '21 at 18:26

1 Answers1

-1

Yes, that is correct as per JDBC specification in Java it says this.

schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search

but PostgreSQL implementation PgDatabaseMetaData doesn't seem to be honoring this.

getTables method has below check, where it is checking for NULL as well as Non empty String. ( may be this is how PostgreSQL implemented it)

  if (schemaPattern != null && !schemaPattern.isEmpty()) {
      select += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
    }

PgDatabaseMetaData implementation code from Github

Janardhan
  • 89
  • 3
  • 1
    This is correct, as there is no such thing as a table without a schema in Postgres. So passing `""` could never return anything. –  Nov 17 '21 at 18:42