savo.la

2015-01-23

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.