idiom to treat empty result set as zero

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?

I think you want coalesce outside the subquery expression.

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

StackExchange Database Administrators Q#76438, answer score: 5