For PostgreSQL ResultSetMetaData.getColumnType() function returns same value (Types.TIMESTAMP) for TIMESTAMPTZ and TIMESTAMP columns

Viewed 260

I have database table which has two columns. One is TIMESTAMPTZ the other one is TIMESTAMP. When i read column type with getColumnType() function of JDBC, it retuns 93 (Types.TIMESTAMP) for both columns. But when you select getColumnTypeName() function it returns different values.

Here is sample code:

try {
    Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/tztestdb", "u", "p");

    Statement statements = connection.createStatement();
    ResultSet resultSet = statements.executeQuery("SELECT * FROM TZTEST");

    ResultSetMetaData metaData = resultSet.getMetaData();

    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        String columnName = metaData.getColumnName(i);
        String type = metaData.getColumnTypeName(i);
        int typeNo = metaData.getColumnType(i);

        System.out.println(columnName + " > " + type + " > " + typeNo);
    }

    connection.close();
} catch (Exception e) {
    e.printStackTrace();
}

and here is the output:

datetimetzcol > timestamptz > 93
datetimecol > timestamp > 93

Is there an easy way of recognizing/detecting the difference without making text compare?

0 Answers
Related