Case when then in postgresql doesn't work as expected (on null value)-CodePudding

I have this table called people

id	name	lastname
1	John	Smith
2	Robert	Williams
3	Peter	Walker

if I run the query

select CASE WHEN id is null THEN '0' ELSE id END as id
from people 
where id='2'

The result is: | id | ---- | 2

I want to display id as 0 when it is Null in the table, but when I run

select CASE WHEN id is null THEN '0' ELSE id END as id
from people 
where id='4'

id

My expected result is: | id | ---- | 0

CodePudding user response：

A top-level SQL query that doesn't match any rows will return zero rows, which is different from returning NULL. This makes sense, because it lets you distinguish between the result for SELECT email FROM users WHERE id=4 when there is no such user and the result when there is a user but their email is null.

However, a subquery that returns no rows will evaluate to NULL the way you expected. So you can rewrite your code like this:

SELECT COALESCE( (SELECT id FROM people WHERE id = '4'), 0 );

COALESCE(x,y) is shorthand for CASE WHEN x IS NULL THEN y ELSE x END. It's helpful in cases like this where the expression for x is long and you don't want to have to write it twice.