Updating Sequences

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. With an OpenERP Publishers Warranty, this won't be an issue for users of OpenERP.

This issue can exist in any data conversion process using a Microsoft SQL -  SQL Server or 'Sequel Server', Oracle, IBM DB2, or MySQL, and PostgreaSQL. 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:

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 it is 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, 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')

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.