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 foridwould be 897 on the next insert. Use the following command to reduce the sequence value for theusers` table by 96.

select setval('users_id_seq',(select max(id) from users));


+--------+
| setval |
+--------+
| 800    |
+--------+
1 rows in set (0.04 sec)


Jeff

Get our stories delivered

From us to your inbox weekly.