What is the PostgreSQL equivalent for ISNULL()

279
votes

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 ?

5
sql-serverpostgresqlnull
No you can't do that in MSSQL. That code will not compile. ISNULL takes two arguments and returns the second is the first is null, otherwise the first. - GSerg
@GSerg, you are right. fixed that. - Byron Whitlock
Gserg and Byron yes you can see here Example from my PC SELECT isnull( a.FechaEntregada ,'') as test from dbo.Amonestacion a msdn.microsoft.com/en-us/library/ms184325.aspx - Juan

5 Answers

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

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias
80
votes

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.

30
votes

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...
16
votes

Try:

SELECT COALESCE(NULLIF(field, ''), another_field) FROM table_name
-8
votes

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.