Table Name alias with Postgres JDBC

Question

We have a method that consumes the table name and operates on the columns.This method previously was working with MySQL but gives blank as the table name with postgres.

I am providing a pretty naive SQL query below just to demonstrate.

select * from ((select * from users2 limit 1) 
union (select * from users limit 1)) as sub;

Code to demonstrate getting table name.

 try{
        connection = DriverManager.getConnection(
                connectionURL, dbUserName,
                dbPassword);



        PreparedStatement psmt = connection.prepareStatement("select * from ((select * from users2 limit 1) union (select * from users limit 1)) as sub");

        ResultSet rs = psmt.executeQuery();

        ResultSetMetaData metaData = rs.getMetaData();

        System.out.println("Table Name");
        System.out.println("----------");

        System.out.println(metaData.getTableName(1));


    }

In MYSQL with the connection parameter as ?useOldAliasMetadataBehavior=true it prints the table name as sub.But in Postgres it prints out an empty string.

I even tried out recursive CTE as below.

with sub as((select * from users2 limit 1) 
union (select * from users limit 1)) select * from sub;

Questions

• How to get the table name as the alias name? In MYSQL it is controlled by a parameter, is there anything equivalent for Postgres ?
• Can anything in the query be changed so that the result set metadata returns the alias table name.

Note that I don't want a view here, this is just one time use.I cannot also fix the underlying method since it incur code changes to all of the consuming code.

Is there any quick way to fix this?

Answer 1

The ResultSetMetaData.getTableName is not supposed to return the alias of the table, it is supposed to return the actual source table name if available. This is also the reason why in MySQL you need to explicitly use useOldAliasMetadataBehavior=true to get this info.

The behavior of PostgreSQL is better from the perspective of the JDBC specification. A union can't have table names for the columns, because the values in a single columns in the result set could be coming from entirely different tables. As far as I know PostgreSQL does not have an option to return the information you expect.