Sunday, May 13, 2012

How To Reset Sequences in PostgreSQL

I use a lot of "serial" data type fields, sometimes the nextval of my sequence doesn't reflect the max() of my table. Since I do a lot of moving data around these can get messed up. The basic command that you want to use to reset a sequence is:
select setval('sequence_name', value);
However, the cool thing about Postgres is that can be a query too so I use:
select setval('sequence_name', select max(column_name) from tablename);
So I wrote a little perl script to look through all of the columns that have a default like "nextval" (which if you look in the database is how a serial column actually looks) and sets the sequence to the max of the value in that table. You can get it here
reset_requences.pl -d  [-s ] [-u ] [-p ]
If you specify a database it will reset all of the columns in the database If you specify a schema it will reset all of the columns within a specific schema. If you specify a user it will reset all of the columns for a specific user.

1 comment:

  1. Thanks!
    In the current psql (9.6.4) I have to put extra () around subquery:
    select setval('sequence_name', (select max(column_name) from tablename));

    ReplyDelete

Note: Only a member of this blog may post a comment.

Web Statistics