When fetching a BOOLEAN data type for a column, this is identified as a BIT data type by java.sql.Types

[ocracoke]

Describe the issue
When parsing the column types from the ResultSetMetaData object returned by java.sql.ResultSet, BOOLEAN columns are being returned as BIT column types.

Driver Version?
42.5.0

Java Version?
OpenJDK 17

OS Version?
Windows 10

PostgreSQL Version?
15.4

To Reproduce
Use the below MVE to parse a BOOLEAN column and identify the type.

Expected behaviour
The below should return "This is a boolean"
The below currently returns "This is not a boolean"

Logs
N/A

MVE

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;

public class PGParseBoolean {
	
    public static void main(String... args) throws Exception {

        String url = <redacted>

        Properties props = new Properties();
        props.setProperty("user", <redacted>);
        props.setProperty("password", <redacted>);
        
        try (Connection conn = DriverManager.getConnection(url, props)) {
            try (Statement statement = conn.createStatement()) {
                try (ResultSet rs = statement.executeQuery("select boolcolumn from example")) {
                	int type = rs.getMetaData().getColumnType(1);

    		        if (type == Types.BOOLEAN) {
    			        System.out.println("This is a boolean");
    		        } else {
    			        System.out.println("This is not a boolean");    					
    		        }
                }
            }
        }
    }
}

[jorsol]

The JDBC specification uses BIT and BOOLEAN types interchangeably, you can check it in Appendix B of the spec the Data Type Conversion Tables.

Sadly this is a legacy behavior of the spec and we have to live with it, in other words, you need to change your check to if (type == Types.BOOLEAN || type == Types.BIT) to correctly cover the boolean data type.

Anyway, FTR, I opened a PR almost 7 years ago changing the data type returned to Types.BOOLEAN, because even if they can be used interchangeably I do believe that since PostgreSQL does have a native boolean data type, the correct one should be Types.BOOLEAN.

But this PR is not accepted as there is a marginal breaking change in code that depends on Types.BIT instead of Types.BOOLEAN (your opposite case), that's why in any case is always recommended to check for (type == Types.BOOLEAN || type == Types.BIT).

[davecramer]

@jorsol thanks for answering. We should probably document that in the code as I didn't recall this detail
At any rate the offending code is

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java

Line 100 in 8afde80

{"bool", Oid.BOOL, Types.BIT, "java.lang.Boolean", Oid.BOOL_ARRAY},

[ocracoke]

The JDBC specification uses BIT and BOOLEAN types interchangeably, you can check it in Appendix B of the spec the Data Type Conversion Tables.

Sadly this is a legacy behavior of the spec and we have to live with it, in other words, you need to change your check to if (type == Types.BOOLEAN || type == Types.BIT) to correctly cover the boolean data type.

Anyway, FTR, I opened a PR almost 7 years ago changing the data type returned to Types.BOOLEAN, because even if they can be used interchangeably I do believe that since PostgreSQL does have a native boolean data type, the correct one should be Types.BOOLEAN.

But this PR is not accepted as there is a marginal breaking change in code that depends on Types.BIT instead of Types.BOOLEAN (your opposite case), that's why in any case is always recommended to check for (type == Types.BOOLEAN || type == Types.BIT).

Whilst I understand the legacy of this (namely here upon investigation: https://www.postgresql.org/message-id/dm2umf$sga$1@sea.gmane.org and your previous issue), is there any documentation that explicitly calls this out? I don't think I see it here - https://jdbc.postgresql.org/documentation/query/

[davecramer]

@ocracoke Apparently no documentation. We do welcome PR's