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

Solution

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

Comments

[isapir]

@laurenz-albe That's exactly what I wrote if you re-read my answer, and the OP. The OP is looking for a function comparable to IFNULL()

[Ugy Astro]

Thanks @isapir, Your answer is what I need. When I try as what you say, I can see the different, especially about duration when script was being execute.