On Data migration, getting error: operator does not exist: boolean = integer Hint: No operator matches the given name and argument types

Question

After migrating database mysql v5 to postgres v12, Java Spring application is showing below error: ERROR: operator does not exist: boolean = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Answer 1

Boolean type checking is vary from database to database (ie. mysql to postgres). Consider the below example what I experienced. Base entity class BaseEnity {} has a column a active boolean type and Order {} entity class extends that class. To select all active orders, the mysql query was:

select * from Order where active = 1

But when migrate the database to postgres it did not work. In postgres, it shows the error operator does not exist: boolean = integer. As postgres expects the query:

select * from Order where active = true

Since, postgres expects boolean value true/false, but in SQL query, the value was set to integer type 1, we experienced error with the hint.