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

This is happening because you have created a column in a PostgreSQL table with boolean type. In MySQL, boolean values are represented as integer values (often bit, to conserve space), and there's no implicit conversion in PostgreSQL:

psql (12.4)
Type "help" for help.

postgres=# select true = 1;
ERROR:  operator does not exist: boolean = integer
LINE 1: select true = 1;
                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# 

You can write a function that converts a bit to boolean (and also the other way around), then create an implicit cast:

-- you'll need to write your own `bit_to_boolean()` function
CREATE CAST (BIT AS BOOLEAN)
  WITH FUNCTION bit_to_boolean(BIT)
  AS IMPLICIT;

That might be too much work. You might be better off converting your int to String in Java, then do the comparison that way;

postgres=# select true = 1;
ERROR:  operator does not exist: boolean = integer
LINE 1: select true = 1;
                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# select true = '1';
 ?column? 
----------
 t
(1 row)

postgres=# select true = 't';
 ?column? 
----------
 t
(1 row)

postgres=# select true = 'f';
 ?column? 
----------
 f
(1 row)

postgres=# select true = '0';
 ?column? 
----------
 f
(1 row)

Another way to possibly work around the issue you're seeing is by editing your Java code to compare true/false keywords instead of an integer:

-- do this
SELECT * FROM table WHERE bool_col = <true/false>;

-- instead of this
SELECT * FROM table WHERE bool_col = <val>;

Answer 2

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.