About COALESCE in PostgreSQL and handling Null Values

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

Solution

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.