About COALESCE in PostgreSQL and handling Null Values

Question

I was practicing SQL with PostgreSQL and I got stuck using COALESCE, maybe someone can tell me where I am going wrong.

I have a table with four columns: title, height_cm, length_cm, and width_cm. I want to multiply three last ones and get the column "size". The problem is that the whole dataset has many NULL values and 0s which I would like to skip and return the biggest value possible with the three columns (i.e. if only height has value, return that value, if the three columns have values multiply the three). If no column has a value return "NO VALUE FOUND".

I was not able to manage "skipping" the Null values (I haven't really started with the 0s yet since I am stuck in the beginning). I thought that this would have done the trick but it did not work.

 SELECT title, height_cm, length_cm, width_cm, 
        COALESCE(
        height_cm * length_cm * width_cm, 
        length_cm * width_cm, 
        height_cm * length_cm, 
        height_cm * width_cm, 
        height_cm, 
        length_cm, 
        width_cm, 'NO VALUE FOUND') AS size
FROM artworks
ORDER BY size ASC
LIMIT 10

First of all, the system does not recognize "NO VALUE FOUND" (but when I add a 0 instead, it recognizes it), secondly, when I take the text out, the system still considers the NULL values. I am able to make it work adding

WHERE height_cm IS NOT NULL AND length_cm IS NOT NULL AND width_cm IS NOT NULL

But I thought that the main idea of COALESCE was to be able to skip the NULL values. Any suggestions?

Regarding the 0s, if I add:

WHERE height_cm != 0 AND length_cm != 0 AND width_cm != 0

I lose the rows that have values but which also have one 0.

Thanks!

EDIT with the solution from the answers

In the end I used a CTE in combination with the answers from the people who helped below, this is the final query:

WITH query AS (SELECT title, height_cm, length_cm, width_cm, 
        (CASE WHEN height_cm IS NULL AND length_cm IS NULL AND width_cm IS NULL
        THEN 0
        ELSE (COALESCE(height_cm, 1) * COALESCE(length_cm, 1) * COALESCE(width_cm, 1))
        END) AS size
        FROM artworks)
        
SELECT *
FROM query
WHERE size > 0
ORDER BY size ASC
LIMIT 10

Answer 1

The datatypes of all elements passed to coalesce should be the same, but I would apply coalesce to each column separately and use the default value of 1 if it's null:

coalesce(height_cm, 1) * coalesce(length_cm, 1) * coalesce(width_cm, 1) as size

which is logically the same as what you've coded.

You'll need a special check in case they are all null, for which you may (ab)use coalesce too:

case
    when coalesce(height_cm, length_cm, width_cm) is null then null -- or 0
    else coalesce(height_cm, 1) * coalesce(length_cm, 1) * coalesce(width_cm, 1)
end as size

You'll have to pick either null or some special value (eg 0) when they are all null. You may render it as "NO VALUE FOUND" to the user as you wish.

Answer 2

"NO VALUE FOUND" is a string type on the other hand 0 is numeric that's why when you are using 0 it does not create problem because in case of 0 all the data for this column is numeric data type but when you are trying to use string it has been created issue of mixing two different data types which will not be allowed by sql engine

Answer 3

I agree with Bohemian on the use of a single expression. However, I think the final expression you want returns a string:

(case when height_cm is null and length_cm is null and width_cm is null
      then 'NO VALUE FOUND'
      else (coalesce(height_cm, 1) * coalesce(length_cm, 1) * coalesce(width_cm, 1))::text
 end) as size

Or, just live with a NULL value meaning that the value is not available.

Answer 4

It sounds like the function you actually want to use here is GREATEST:

SELECT
    title,
    height_cm,
    length_cm,
    width_cm, 
    COALESCE(
        GREATEST(
            height_cm * length_cm * width_cm,
            length_cm * width_cm,
            height_cm * length_cm,
            height_cm * width_cm,
            height_cm,
            length_cm,
            width_cm)::text, 'NO VALUE FOUND') AS size
FROM artworks
ORDER BY size
LIMIT 10;

The GREATEST function will automatically pick up on the largest non NULL value already. So, you only need COALESCE here as a catch-all for the case where the height, length, and width, all happen to be NULL at the same time.