ansaurus

Question

What is the PostgreSQL equivalent to ISNULL()

Answer 1

+12 A:

SELECT case field WHEN field3 IS NULL THEN 'Empty' END as fieldAlias

Or more idiomatic:

SELECT coalesce(field3, 'Empty') as fieldAlias

Kyle Butt 2010-02-06 20:02:02

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

Alison R. 2010-02-06 20:09:04

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

Kyle Butt 2010-02-06 20:14:34

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 2010-02-06 20:35:12

`COALESCE` is ANSI standard, and supported SQL Server 2000+

OMG Ponies 2010-02-06 23:00:11

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

Emtucifor 2010-02-07 00:03:49

Answer 2

+1 A:

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.

2010-03-06 10:37:02

ansaurus

tags:

views:

answers:

What is the PostgreSQL equivalent to ISNULL()

related questions