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

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.

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

How about this?

CREATE OR REPLACE FUNCTION equal_int_bool(x int, y bool) 
RETURNS BOOLEAN AS $$ 
begin
    return x = y::int;
end;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

CREATE OPERATOR = ( 
  leftarg = INTEGER, 
  rightarg = BOOLEAN,
  procedure = equal_int_bool);

CREATE OR REPLACE FUNCTION equal_bool_int(x bool, y int) 
RETURNS BOOLEAN AS $$ 
begin
    return x::int = y;
end;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

CREATE OPERATOR = ( 
  leftarg = BOOLEAN, 
  rightarg = INTEGER,
  procedure = equal_bool_int);

Example truths:

> select 0=false a, false=0 b, 1=true c, true=1 d;
 a | b | c | d
---+---+---+---
 t | t | t | t

Expect false for all other comparisons:

 > select 1=false a, 0=true b, 2=true c, -1=true d, true=-1 e;
 a | b | c | d | e
---+---+---+---+---
 f | f | f | f | f

Note: I'm a relative Postgresql novice, and am not recommending you create tons of random implicit conversions in your database, but thought it interesting that this was possible. Feel free to warn of dangers.

P.S. I'm deliberating whether a better semantic is 0=false, non-zero=true. Depends on your use case.