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.

No comments:

Post a Comment

Web Statistics