Set null values to a default in Postgresql case statement

Question

I'm trying to set a default value when my attribute comes up null in the database:

case categories.rental_price_percentage when null then '15.0' else categories.rental_price_percentage end rental

However, the replacement value isn't taking hold when I hit a null value (the rest is working though), it only displays null:

{
 id: "3",
 category_name: "Chairs",
 rental: null
}

How can I get the default value in and working?

Answer 1

WHEN NULL in a simple CASE expression (it's not a statement) will never match because that would require, in your case, categories.rental_price_percentage = NULL to be true. But the result of comparison operations like =, >, ... against NULLs are NULL i.e. not true -- on only one or both sides, so even NULL = NULL is not true.

To check if an expression evaluates to NULL IS NULL is used.

So you could use a searched CASE expression:

CASE
  WHEN categories.rental_price_percentage IS NULL THEN
    15.0
  ELSE
    categories.rental_price_percentage
END rental

But replacing NULLs is such a common task, that there exists a function to do that, coalesce(). It returns the first of the arguments it gets passed, that is not NULL.

So in your case you could simply use:

coalesce(categories.rental_price_percentage, 15.0) rental

Note that I also removed the single quotes around 15.0 as it's presumably meant as a numeric value, not a string.