Idiom to treat empty result set as zero

Question

This is obviously a SSCCE.

I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple:

CREATE TABLE inventory(inventoryDate INTEGER, numOfItems INTEGER);
ALTER TABLE inventory ADD PRIMARY KEY (inventoryDate);

Now I have some entries:

INSERT INTO inventory(inventoryDate, numOfItems) VALUES(1,250),(2,275)

What the above says is that on time 1 there were 250 items in the warehouse and on time 2 there were 275.

Now I want to test whether, at the latest date, the number of items in the warehouse exceeded 1000:

SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

The above seems to work. However, on the edge case that there are no entries at all in the inventory table it's not working:

DELETE FROM inventory;
SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

... returns:

?column?
(null)

I would like to treat the edge case where there are no entries at all as denoting zero (0) items. I know I can always initialize the inventory table with a fake first entry with a value of zero but like I said this is an SSCCE (in my real case this table is really a view which I don't want to modify).

So I end up writing the query like this:

WITH cte AS (SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)
SELECT 1000 < (
CASE 
WHEN NOT EXISTS(SELECT 1 FROM cte) THEN 0
         ELSE  (SELECT numOfItems FROM cte)
END)

The above does seem to work. But, is there a more idiomatic way? Something like a COALESCE but for an empty result set instead of null?

Answer 1

I think you want coalesce outside the subquery expression.

SELECT 1000 < coalesce((SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1), 0);

Answer 2

COALESCE in an outer SELECT does the job for a single value. Wouldn't work for selecting multiple columns or rows, though.

This technique with UNION ALL works universally:

(
SELECT numofitems   -- or more columns
FROM   inventory
ORDER  BY inventorydate DESC NULLS LAST -- add NULLS LAST if you col isn't NOT NULL!
LIMIT  1            -- or more rows
)
UNION  ALL
SELECT NULL         -- or more columns (matching)
LIMIT 1;

Or SELECT 0 instead of SELECT NULL to save another COALESCE.
Parentheses around first SELECT are not optional.

But consider:

• Return a value if no record is found
• Are results from UNION ALL clauses always appended in order?
• Special variable FOUND not set properly?