I am working on a project of migrating from MySQL to PostgreSQL, some function can't works well in PostgreSQL like IFNULL
function. Some tutorials say that in PostgreSQL we can use NULLIF
to handle it.
When I try I get an issue "argument of NOT must be type boolean, not type integer
".
This is the simple SQL :
SELECT * FROM `tableA` WHERE not(nullif(columnA, 0));
How to solve it? Maybe some one can make an explain how can it works well. Thanks
NULLIF() is very different from IFNULL(). I think that what you want is COALESCE(), which will return the first non-NULL argument (it can have more than 2 arguments):
SELECT *
FROM table_a
WHERE NOT (COALESCE(column_a::boolean, false));
Reference: 9.17.2. COALESCE
Also, in Postgres you need to use true
or false
. 0 and 1 do not work for boolean literals. That's the reason that you get the error:
argument of NOT must be type boolean, not type integer
If column_a
is an integer, then you have to CAST it to boolean
. That's what column_a::boolean
in the example above does. It is equivalent to CAST(column_a AS boolean)
.