I would create a small function with the sequence_name and reference_table as parameters (not tested) ... DECLARE newvalue int; rec record; BEGIN For rec in EXECUTE 'Select into newvalue max(id) as m from '||$2 loop EXECUTE 'ALTER SEQUENCE '||$1||' restart with '||rec.m; End loop; END; Return 0; ... Cheers, marc -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of developer@xxxxxxxxxxxx Sent: Tuesday, December 12, 2006 5:39 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: [GENERAL] resetting sequence to cur max value I am migrating a system from hsqldb to postgresql. I have a bunch of installs of this system live so moving the data is a headache. I was using identities in hsqldb and now I am using sequences. I was able to move all my data over however I am having an issue with the sequences. I default them all to start at a certain number; this works great for a fresh install. However when working with existing data the default is < the current number. ANd I have several installs and they are all different numbers. Is there a way to set it up so it knows to skip past existing ids? I would rather an automated solution but I was even trying something like this: ALTER SEQUENCE seq_address restart with (select max(id) from address) I guess that doesn't work because it wants a constant. Any suggestions? thanks ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
