CODEWITHSUNDEEP
postgresql
uniquegino
uniquegino

Reputation: 2005

In Postgresql, what's the difference between Case When VS CASE columnname WHEN

With the query

SELECT id, name
, CASE name WHEN NULL THEN FALSE ELSE TRUE END as name_constraint1
, CASE WHEN name IS NULL THEN FALSE ELSE TRUE END as name_constraint2
FROM table

I got result as 

id   | name  | name_constraint1 | name_constraint2
---- | ----- |----------------  | ----------------
1    | a     |t                 | t 
2    | b     |t                 | t 
3    |       |t                 | f

My question is why CASE name WHEN NULL THEN FALSE fails to figure out the NULL value in name column, while CASE WHEN name IS NULL THEN FALSE works fine?

Upvotes: 0

Views: 1469

Answers (1)

leonbloy
leonbloy

Reputation: 75916

The "simple" variation of the CASE expression is equivalent to a plain compare (by equality).

Hence

CASE name WHEN NULL THEN FALSE ...

is equivalent to

CASE WHEN name = NULL THEN FALSE ...

which, as you probably know, is not the right way to test for null values, it's not the same as the (right) way:

CASE WHEN name IS NULL THEN FALSE ...

Precisely, the advantage of the general (more verbose, "non simple") form is that it allows arbitrary expressions to be tested, for each case alternative (perhaps totally different). The simple form only admits a single expression that is to be compared to a list of values. In this case, you must use the general form.

By the way, for this particular use (replace a NULL value with some predetermined value), it's better to use the standard COALESCE function, which does precisely that.

Upvotes: 4

Related Questions