PostgreSQL: column "BOOLEAN_VALUE" is of type numeric but expression is of type boolean Hint: You will need to rewrite or cast the expression

Question

I was trying to insert a new data into both database server. This one works in Oracle 11g but does not work in PostgreSQL 9+. I cannot trace the issue as the error says:

column "BOOLEAN_VALUE" is of type numeric but expression is of type boolean Hint: You will need to rewrite or cast the expression.

The column is nullable and in codes below, we do not set any data.

Table Def:(Numeric)
"BOOLEAN_VALUE" NUMBER(1,0)

Domain:
@Column(name = "BOOLEAN_VALUE")
public Boolean getBooleanValue() {
    return booleanValue;
}

public void setBooleanValue(Boolean booleanValue) {
    this.booleanValue = booleanValue;
}

How data insertion:
MyData myData = new MyData();
myData.setMoneyValue("$ 120")
myServiceRepository.save(myData);

We did passing 0 or 1 as default value but none has worked. Hope someone can give me some light.

Answer 1

PostgreSQL do not auto-cast int to boolean automatically, eventually you can create your own cast function.

See: https://www.postgresql.org/docs/9.4/static/sql-createcast.html

Answer 2

For hibernate 6 you can annotate the jdbc type to integer

@JdbcTypeCode(Types.INTEGER)

private boolean booleanColumn;