Some time ago I encountered an unpleasant bug I made in a certain PostgreSQL query. Assume that you have a table with an integer primary key id, autoincremented on every insert.
create table some_table( id serial primary key, content text );
Assume also that in the course of work, you need to “reset” this table once in a while – that is, truncate it and then populate with some data used for development and testing. Of course, after populating it, you want the “next value” of id to be set so that the next insert will use the first available integer value.
The way I usually solved that was using the following query:
select setval('some_table_id_seq', max(id)) from some_table;
It works pretty well except in one case – when the initial contents of the table are empty. It turns out that when you give null as the second argument to setval, it essentially becomes a no-op. The solution is easy:
select setval('some_table_id_seq', coalesce(max(id) + 1, 1), false) from some_table;
This way the table is ready for entering new data whether it is empty or not.
That’s it for today. Happy hacking!