CODEWITHSUNDEEP
sql-serverpostgresqlnull
Byron Whitlock
Byron Whitlock

Reputation: 53851

What is the PostgreSQL equivalent for ISNULL()

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 ?

Upvotes: 327

Views: 387041

Answers (4)

Artur
Artur

Reputation: 575

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...

Upvotes: 34

user2718914
user2718914

Reputation: 261

Try:

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

Upvotes: 26

Jim Clouse
Jim Clouse

Reputation: 8970

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.

Upvotes: 107

Kyle Butt
Kyle Butt

Reputation: 9760

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

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias

Upvotes: 565

Related Questions