IFNULL() Equivalent in PostgreSQL

Question

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

Answer 1

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).