Re: resetting sequence to cur max value

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
developer(at)wexwarez(dot)com
Sent: Tuesday, December 12, 2006 5:39 PM
To: pgsql-general(at)postgresql(dot)org
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/