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 Answers
SELECT CASE WHEN field IS NULL THEN 'Empty' ELSE field END AS field_alias
Or more idiomatic:
SELECT coalesce(field, 'Empty') AS field_alias
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.
How do I emulate the ISNULL() functionality ?
SELECT (Field IS NULL) FROM ...
Try:
SELECT COALESCE(NULLIF(field, ''), another_field) FROM table_name
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.
