What is the PostgreSQL equivalent for ISNULL()

sql-server postgresql null

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 ?

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, you are right. fixed that.

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

Erwin Brandstetter

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

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias

+1 for coalesce. (P.S. You can do that in MS SQL Server, too.)

There are other cases for using IS NULL though, so it's good to know both.

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.

Coalesce() also handles type promotion properly (exactly like UNION SELECT does), while IsNull() does not.

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...

Richard D

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.

Satpal

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...

This emulates the exact functionality of isnull, not sure why it's downvoted

The best answer for the question, of course. This expression is the full equivalent of ISNULL(). COALESCE() is very smart and interesting to know but it cannot perform an ISNULL() while it is closed.

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.

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.

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.

Smern

Try:

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

This is nice as it covers the case when a text field is NOT null, but also 'empty'.

佚

佚名

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.

Please, nobody do this. Use coalesce() instead so your DBA doesn't hate you.

postgres please add isnull so nobody hates anyone.

What is the PostgreSQL equivalent for ISNULL()

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US