Search code examples
postgresqlisnull

MySQL ISNULL SQL code equivalency for PostgreSQL

I am new to PostgreSQL and I am constantly learning. I am following a mini-project guideline that uses MySQL but I prefer working in PostgreSQL. I have read many StackOverflow answers that have helped me in the past, but I am completely stuck on this one. My code is:

Select a.parcelid, a.propertyaddress, b.parcelid, b.propertyaddress,
    ISNULL(a.propertyaddress, b.propertyaddress)
    From public.nashhousing a
    JOIN public.nashhousing b 
        on a.parcelid = b.parcelid
        AND a.uniqueid <> b.uniqueid
    Where a.propertyaddress is null

The error message I keep receiving is:

ERROR:  function isnull(text, text) does not exist
LINE 2: ISNULL(a.propertyaddress, b.propertyaddress)
        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Can anyone please assist me? Thank you!

Solution

  • COALESCE is the sql standard version of this function that probably works in most database. i.e., coalesce(a.propertyaddress, b.propertyaddress)

    COALESCE returns the first non-null value, so it can work with two (as in your case) or more values. We could also, for instance, write coalesce(a.propertyaddress, b.propertyaddress, 'no address!') and it would return the third value if both of the first two are null.

    Here's the official postgres documentation:

    https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL