319

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 ?

Improve this question
3
  • 2
    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
    Commented Feb 6, 2010 at 20:40
  • @GSerg, you are right. fixed that.
    – Byron Whitlock
    Commented Mar 8, 2010 at 19:29
  • 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
    Commented May 31, 2015 at 15:28

4 Answers 4

Reset to default
550 
SELECT CASE WHEN field IS NULL THEN 'Empty' ELSE field END AS field_alias

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias
Improve this answer
7
  • 60
    +1 for coalesce. (P.S. You can do that in MS SQL Server, too.)
    – Alison R.
    Commented Feb 6, 2010 at 20:09
  • 6
    There are other cases for using IS NULL though, so it's good to know both.
    – Kyle Butt
    Commented Feb 6, 2010 at 20:14
  • 39
    I think it's worth noting that it is coalesce that is in SQL standard, with isnull being an MS-specific function that essentially is coalesce with only two parameters.
    – GSerg
    Commented Feb 6, 2010 at 20:35
  • 6
    Coalesce() also handles type promotion properly (exactly like UNION SELECT does), while IsNull() does not.
    – ErikE
    Commented Feb 7, 2010 at 0:03
  • 9
    It is worth pointing out that ISNULL and COALESCE are not the same. IsNull forces the result-type to the type of argument1, while coalesce uses the respective types for each argument. If you just search-and-replace isnull with coalesce, you can potentially get a lot of errors...
    – Stefan Steiger
    Commented Feb 20, 2019 at 16:02
105

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.

Improve this answer
32

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...
Improve this answer
6
  • 7
    This emulates the exact functionality of isnull, not sure why it's downvoted
    – smackshow
    Commented Mar 26, 2013 at 10:38
  • 24
    I don't know what ISNULL you commenters are referring to, but field IS NULL gives a boolean value, while ISNULL in SQL Server operates like COALESCE: it returns one of the non-NULL values. This answer is terribly wrong. See the documentation: ISNULL.
    – jpmc26
    Commented Feb 4, 2015 at 4:03
  • 15
    I suspect these commenters are MySQL users who didn't realize the question starts with, "In MS SQL Server, ..." MySQL has an ISNULL() function that takes a single argument and returns 0 or 1. T-SQL's version takes two arguments and behaves like COALESCE or Oracle's NVL.
    – David Noha
    Commented Jun 8, 2016 at 22:15
  • 3
    The answer is wrong. Though it was explained in above comments, some people are still wondering why. Weird...
    – greatvovan
    Commented Oct 25, 2018 at 1:09
  • 4
    greatvovan, Whether or not this was the intent of the original poster, I believe what people are wanting is the answer to "How do I check if a field is null", not necessarily "How exactly does the ISNULL function work". That was the case with me and this answer is perfect for that.
    – Freeman Helmuth
    Commented Mar 1, 2019 at 22:21
24

Try:

SELECT COALESCE(NULLIF(field, ''), another_field) FROM table_name
Improve this answer
2
  • 7
    This is nice as it covers the case when a text field is NOT null, but also 'empty'.
    – soulia
    Commented Aug 20, 2014 at 14:45
  • 2
    This is a nice addition but confuses the original question by essentially tacking on "how do I coerce non-null fields to null?"
    – plyawn
    Commented Oct 1, 2023 at 14:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.