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;
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;