postgresqlkotlinmybatisspring-mybatismybatis-mapper

Postgresql - fix Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean


I've got a Postgres table with an integer column and a MyBatis bean with a Boolean type.

val damaged: SqlColumn<Boolean> = column("damaged", JDBCType.TINYINT)

I have columns defined like above in my mapper helper.

This works great in mssql and mysql, but postgresql says:

Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I can't figure out how to fix this, if I try to add an explicit cast postgres says the cast already exists for int to boolean. How do I support the operators = and <> (I have another test complaining about "not equals"?

I'm already doing this for smallint to bool, but if I do this for int or integer to bool I get the error about the cast already existing:

CREATE OR REPLACE FUNCTION boolean1(i smallint) RETURNS boolean AS '
            BEGIN
                RETURN (i::smallint)::int::bool;
            END;
            ' LANGUAGE plpgsql;

            CREATE CAST (smallint AS boolean) WITH FUNCTION boolean1(smallint) AS ASSIGNMENT;

Solution

  • I guess you compare the bean property with the column in the SQL. e.g.

    WHERE damaged = #{damaged}
    

    As the bean property type is Boolean, MyBatis, by default, uses BooleanTypeHandler which internally calls java.sql.Statement#setBoolean(). While some databases allow this comparison, PostgreSQL does not, apparently [1].

    You may have to write a custom type handler and do something like below instead of calling setBoolean().

    ps.setInt(i, parameter.booleanValue() ? 1 : 0);
    

    I'm not familiar with mybatis-dynamic-sql, but it seems possible to initialize SqlColumn with the type handler.

    column("damaged", JDBCType.TINYINT, "pkg.MyBooleanTypeHandler")
    

    [1] You may get practically the same error if you execute the following SQL with psql.

    select * from tbl where damaged = 1::boolean;