Home > database >  MySQL ISNULL SQL code equivalency for PostgreSQL
MySQL ISNULL SQL code equivalency for PostgreSQL

Time:10-16

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!

CodePudding user response:

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

CodePudding user response:

I think your example is wrong in that you are looking for a alternate to IFNULL not ISNULL as ISNULL takes only a single argument. The alternate and SQL standard function is COALESCE.

  • Related