JDBC problem in 10.3 / 10.4

I am using Connection.getMetaData().getTables(...) to auto generate

SQL statements (INSERT, UPDATE and DELETE). When upgrading

underlying databases to 10.3 and 10.4 on different servers, I started

getting problems. After some debugging I found that the meta

data returned by the same JDBC driver for the different server

versions are different.

If you debug the code below (I am using Eclipse) and

step through the code, the "originalQuery" property of the resultset

(which you can see in "rs" variable properties) differs. I have tested

the postgresql-42.2.2.jre7.jar and postgresql-9.4.1212.jar against

both servers and got the same problem.

Test Code: (Replace createConnection() to whatever you need)

try (Connectgion con = createConnection();

ResultSet rs = con.getMetaData().getTables(null, null, null, new String[] { "TABLE" })) {

while (rs.next()) {

String catalogue = rs.getString("TABLE_CAT");

String schema = rs.getString("TABLE_SCHEM");

String name = rs.getString("TABLE_NAME");

String type = rs.getString("TABLE_TYPE");

System.out.println(catalogue + " / " + schema + " / " + name + " / " + type);

}

}

"originalQuery" used when connecting to 9.5 server:

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, -- CASE STATEMENTS --

FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)

LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')

LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')

WHERE c.relnamespace = n.oid AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname 'information_schema' ) )

ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

"originalQuery" used when connecting to 10.4 server:

SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, -- CASE STATEMENTS --

FROM pg_class c

WHERE true AND (false OR ( c.relkind = 'r' AND c.relname !~ '^pg_' ) )

ORDER BY TABLE_TYPE,TABLE_NAME

This shows clearly that LEFT JOINs are left out for 10.4 and NULL is simply returned for some columns. The full

statement actually works for 10.5, so there must be some other condition which determines the SQL used.

Kind regards

John Bester

Nexti SA PTY LTD

Tel. (023) 313 3011

john(at)softco(dot)co(dot)za