How to query a boolean column using an integer with Postgres?-CodePudding

I post a similar question previously, but have opened another question to be more specific as the previous one gave me a solution but I have now encountered another problem.

We have an existing Oracle database that had boolean columns defined like so

CREATE TABLE MY_TABLE 
(    
SOME_TABLE_COLUMN NUMBER(1) DEFAULT 0 NOT NULL,
etc..

And with the corresponding java field defined like private boolean someTableColumn; I've come to understand this is because Oracle does not have a Boolean datatype, but under the hood does the conversion from boolean to integer when inserting data, and the reverse when retriving data.

This has caused an issue when I have been working on migrating our database from Oracle to Postgres. Thanks to answers on my previous question, I have migrated the column type from NUMBER(1) to BOOLEAN. This has solved the issue with inserting data. However, our codebase uses JDBCTemplate and we unfortunately have hundereds of hardcoded queries in the code that make queries like SELECT * FROM MY_TABLE WHERE TABLE_COLUMN=1.

When these run against the Postgres DB, I get the following error ERROR: operator does not exist: boolean = integer. We have a requirement to have backwards compatability with Oracle, so I can't simply update these queries to replace 1 and 0 with TRUE and FALSE respectively.

Is there a way I can configure Postgres so it can do a conversion behind the scenes to resolve this? I have looked at casts but I don't really understand the documentation and the examples given don't seem to match my use case. Any help is appreciated.

CodePudding user response：

Can you try to use '0' and '1' instead of 0 and 1 in your requests ? I used to work on apps compliant with both Oracle and Postgresql using this syntax. Apps were using JPA but can say with certainty that we were also using this syntax with nativeQuery = true.

Note: I would have posted this as a comment but I don't have the required reputation to do so, hence the post as an answer

CodePudding user response：

This is one of the issues I have with the concept of "database independence". It simply does not exist. Vendors implementation often simply vary too much. In this case Postures to the rescue, perhaps but also perhaps extreme: create you own create your own operators. Proceed with caution:

-- function to compare boolean = integer 
create or replace 
function"__bool=int"( b boolean, i int) 
   returns boolean
  language sql
as $$
    select (b=i::boolean);
$$;  

-- create the Operator for boolean = integer
create operator = (
       leftarg = boolean
     , rightarg = int
     , function =  "__bool=int"
    , commutator = =
);

The above will not allow your code: SELECT * FROM MY_TABLE WHERE TABLE_COLUMN=1 (see demo here).
However, this road may lead to unexpected twists, and lots of function/operator pairs. For example the above does not support SELECT * FROM MY_TABLE WHERE TABLE_COLUMN<>1. That requires another function/operator combination. Further I do not see a retrieval function that converts a boolean back to an integer. If you follow this path be sure to massively test your boolean-to-integer (integer-to-boolean) operations. It may just be better to just byte the bullet and updated those few queries (you did say hundreds not thousands) as @mlogario suggests.