问题:

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 ?

回答1:

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

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias

回答2:

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.

回答3:

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...

回答4:

Try:

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

回答5:

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.