Reset Postgres table sequence after DELETE
When deleting recent records from a table, you can reset it's sequence counter if auto-increment field continuity is important to you. Consider actions on the following table, users:
select max(id) from users;
+-----+ | max | +-----+ | 896 | +-----+ 1 rows in set (0.03 sec)
Then, some delete action against most recent rows:
delete from users where created_at > timestamp 'yesterday'; 96 rows in set (0.15 sec)
The next auto-increment value for id would be 897 on the next insert. Use the following command to reduce the sequence value for the users table by 96.
select setval('users_id_seq',(select max(id) from users));
+--------+
| setval |
+--------+
| 800 |
+--------+
1 rows in set (0.04 sec)
