What is the PostgreSQL equivalent for ISNULL()

Question

In MS SQL-Server, I can do:

SELECT ISNULL(Field,'Empty') from Table

But in PostgreSQL I get a syntax error. How do I emulate the ISNULL() functionality ?

Answer 1

Try:

SELECT COALESCE(NULLIF(field, ''), another_field) FROM table_name

Answer 2

Use COALESCE() instead:

SELECT COALESCE(Field,'Empty') from Table;

It functions much like ISNULL, although provides more functionality. Coalesce will return the first non null value in the list. Thus:

SELECT COALESCE(null, null, 5); 

returns 5, while

SELECT COALESCE(null, 2, 5);

returns 2

Coalesce will take a large number of arguments. There is no documented maximum. I tested it will 100 arguments and it succeeded. This should be plenty for the vast majority of situations.

Answer 3

SELECT CASE WHEN field IS NULL THEN 'Empty' ELSE field END AS field_alias

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias

Answer 4

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...

Answer 5

Create the following function

CREATE OR REPLACE FUNCTION isnull(text, text) RETURNS text AS 'SELECT (CASE (SELECT $1 "
    "is null) WHEN true THEN $2 ELSE $1 END) AS RESULT' LANGUAGE 'sql'

And it'll work.

You may to create different versions with different parameter types.