Updating Sequences Automatically in Postgresql

So about the second or third time you write something from scratch and it is a useful something and you’re thinking that next time you don’t want to write it again… well that’s a good time to record it somewhere. So recently we were doing a data migration project from version OpenERP 5.x to OpenERP 6.x.  We were doing this for our own company because version 6 of OpenERP hasn’t been released yet, but we want to use it as beta testers so we can contribute to, and help with development.  With an OpenERP Publishers Warranty, this won’t be an issue for users of OpenERP.  It is only an issue for us because we are testing on a non-released version, version 6.0. This issue can exist in any data conversion process using a Microsoft SQL -  SQL Server or ‘Sequel Server’, Oracle, IBM DB2, or MySQL and Postgresql. During the data conversion from one database to another database you often run into serial key, or sequence issues. OpenERP runs on Postgresql so this is written for Postgresql Sequence or Serial Key Issues. When you load data into a table the data includes ID’s from your old database, for example with a user id 100. There may have not been any id’s in the database you’re putting the data into so you have to set the sequencer to start at your highest ID value.  So if the highest id value in the table was 100 but your serial key is only 99 for example next time you add a record you will get an error message something like:

An error has occurred:

ERROR:  duplicate key value violates unique constraint "res_users_pkey"

The fix to this if you have only one is to simply do an alter sequence statement something like:

alter sequence mytable_seq restart with 100

However, if you have to do it like 100 times on a big database integration or simply want to have it as part of your script that moves data you could use a function to do that.  That function is the one I have written at least three times and the purpose of this post.  I hope it isn’t a standard function already in there that I don’t know about, if so, then maybe this post will encourage someone to tell me.  Anyway I am recording it here for my own re-use in the future. First you must install plpgsql in Linux you do this like this

createlang plpgsql database_name

You can do that from the command line as postgres or sudo as postgres into the database that you want to use plpgsql in. Then you can create this function

--drop function update_serial_key(character varying, character varying);
create function update_serial_key(tablename varchar, sequencename varchar)RETURNS integer AS $$

declare
 table_name alias for $1;
 c integer ;
Begin
EXECUTE 'select max(id) from '
|| table_name
 INTO c;

execute 'alter sequence '
||$2
||' restart with '
||c;
return c;
End
$$ LANGUAGE plpgsql;

Then at the end of your updates to a table, you can update the sequence by calling that function like this:

select update_serial_key('res_users', 'res_users_id_seq')

That is select update_serial_key (‘table_name’,'sequence_name’) So in my case at the end of migrating the users data from res_users of OpenERP 5 to OpenERP 6, I run that command and it automatically fixes the res_users_id_seq sequence to where it is suppose to be.  This is very handy for large data migration or upgrade projects when you might use it over and over and over again.