PostgreSQL – Treating Empty Result Set as Zero

postgresqlpostgresql-9.1

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?

Best Answer

I think you want coalesce outside the subquery expression.

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

Related Solutions

Postgresql – constraint view CASE values

You could add a column significance to your crimetypes table, with increasing values for the crimes (ie. 1 for INFRACTION,2 for 'MISDEMEANOR', 3 for 'FELONY', etc.)

Then your query could be written as:

CREATE VIEW person_assessment AS
SELECT p.name, 
       CASE COALESCE(MAX(c.significance), 0)
           WHEN 0 THEN 'exemplary'
           WHEN 1 THEN 'law-abiding'
           WHEN 2 THEN 'relatively law-abiding'
           WHEN 3 THEN CASE WHEN COUNT(CASE WHEN c.significance >= 3 THEN 1 END) <= 4 
                                THEN 'felon'
                                ELSE 'career criminal'
                       END
                  ELSE 'unhandled case'
       END AS citizen_type
FROM person AS p 
  LEFT JOIN infraction AS i 
    ON i.person = p.name
  LEFT JOIN crimetypes AS c
    ON i.crimetype = p.crimetype 
GROUP BY p.name ;

This is more of a way to have the query "tidied up", readable and easy to change in the future.

It may not be as efficient as your query though, depends on the data. If no person in the database has any crime stored, only the first WHEN of your case has to be run. If on the other side, most persons in the database have felonies, several correlated subqueries will have to be run so the above strategy will probably be more efficient as it uses only a join and a GROUP BY.

About question 2, you can always join this view to another table with descriptions.

SELECT 
    ....
FROM person_assessment AS pa
  JOIN citizen_types AS ct
    ON ct. citizen_type = pa. citizen_type ;

Postgresql – need better db design for project

I think the Notes table is missing a userID FK column (and put an index on it)
You need to identify the owner of the Note

The UserNotes table does not need an ID
just:

NoteID || UserId

That is a sufficient composite PK
And I would do it in that order for quick counting NoteID
select count(*) from UserNotes where NoteID = X would be very very fast

So what 1000 x 1000 rows. You have 1000 x 1000 unique relationships. It is just two Int.

One trick is have a UserID Public ID 0 for all

Just a preference but I don't use plural in a table name
Tables are used to store many rows

Best Answer

Related Solutions

Postgresql – constraint view CASE values

Postgresql – need better db design for project

Related Question