When run the select on Postgres over java
SELECT '' AS COL1, 0 AS COL2 FROM MYTABLE 1=2;
Column type is Types.OTHER both for COL1 and COL2. As it is obvious resultset has not any row.
But if I have rows in result set using the query below:
SELECT '' AS COL1, 0 AS COL2 FROM MYTABLE;
Type of COL1 is still Types.OTHER but COL2 type is Types.INTEGER. In my case I need Types.VARCHAR and Types.INTEGER even result is empty or not.
Are there any configurations on db layer or jdbc url to get Types.VARCHAR and Types.INTEGER both for two queries.
try explicit cast:
SELECT ''::VARCHAR AS COL1, 0::INTEGER AS COL2 FROM MYTABLE;
The Postgres JDBC driver 9.4.1212 reports the type of constant 0 as Types.INTEGER = 4, regardless if there is a result or not (this may also depend on the Server version - I tested with a 9.4 server).
The string constant is still reported as Types.OTHER. For such columns ResultSetMetaData.getColumnClassName(i) returns java.lang.String, so if you encounter a Types.OTHER you can use this information to recognize the column as a character column.
If you want the ResultSetMetaData to return the wanted type, grab the sources from Github and patch the ResultSetMetaData to do this translation.