Counting entries during insert/delete with PostgreSQL
Say we are storing some kind of entries in PostgreSQL, and also need to report changes in their amount. We'll be using this in the examples:
=> CREATE TABLE entries ( id SERIAL PRIMARY KEY, foo TEXT, bar TEXT );
The obvious thing to do is:
=> INSERT INTO entries (foo, bar) VALUES ('baz', 'quux') RETURNING id; id ---- 25 => SELECT count(*) FROM entries; count ------- 13
But I like to avoid multiple commands when possible, so I came up with the following version:
=> INSERT INTO entries (foo, bar) VALUES ('baz', 'quux') RETURNING (SELECT count(*) FROM entries), id; count | id -------+---- 12 | 25
The result is almost the same: since the subquery is executed before the insert, we have to add 1 to the count to get what we want.
The equivalent delete command:
=> DELETE FROM entries WHERE id = 25 RETURNING (SELECT count(*) FROM entries), *; count | id | foo | bar -------+----+-----+------ 13 | 25 | baz | quux
Here we have to subtract 1 from the count.
I hope you find this information nugget helpful.