Обсуждение: 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
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of developer@wexwarez.com Sent: Tuesday, December 12, 2006 5:39 PM To: pgsql-general@postgresql.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/
developer@wexwarez.com writes: > Is there a way to set it up so it knows to skip past existing ids? Usually you do something like select setval('seq_name', (select max(idcol) from table) + 1); after loading data into the table. regards, tom lane
Awesome. Thanks tom. By the way I am still trying to find a yum install for 8.2 for centos...anyone? > developer@wexwarez.com writes: >> Is there a way to set it up so it knows to skip past existing ids? > > Usually you do something like > > select setval('seq_name', (select max(idcol) from table) + 1); > > after loading data into the table. > > regards, tom lane >
On Tue, Dec 12, 2006 at 12:19:56PM -0500, Tom Lane wrote: > developer@wexwarez.com writes: > > Is there a way to set it up so it knows to skip past existing ids? > > Usually you do something like > > select setval('seq_name', (select max(idcol) from table) + 1); > > after loading data into the table. Is "+ 1" necessary with the two-parameter form of setval()? According to the setval() doc, "The two-parameter form sets the sequence's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value." I often omit the increment -- am I flirting with danger? test=> CREATE TABLE foo (id serial, t text); NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" CREATE TABLE test=> INSERT INTO foo (id, t) VALUES (1, 'one'); INSERT 0 1 test=> INSERT INTO foo (id, t) VALUES (2, 'two'); INSERT 0 1 test=> INSERT INTO foo (id, t) VALUES (3, 'three'); INSERT 0 1 test=> SELECT setval('foo_id_seq', (SELECT max(id) FROM foo)); setval -------- 3 (1 row) test=> INSERT INTO foo (t) VALUES ('four'); INSERT 0 1 test=> SELECT * FROM foo; id | t ----+------- 1 | one 2 | two 3 | three 4 | four (4 rows) -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes: > On Tue, Dec 12, 2006 at 12:19:56PM -0500, Tom Lane wrote: >> Usually you do something like >> select setval('seq_name', (select max(idcol) from table) + 1); >> after loading data into the table. > Is "+ 1" necessary with the two-parameter form of setval()? Given the docs you quoted, no --- I was just too lazy to look up whether it set is_called or not. With the +1 you don't have to think ;-) regards, tom lane
Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > >> On Tue, Dec 12, 2006 at 12:19:56PM -0500, Tom Lane wrote: >> >>> Usually you do something like >>> select setval('seq_name', (select max(idcol) from table) + 1); >>> after loading data into the table. >>> > > >> Is "+ 1" necessary with the two-parameter form of setval()? >> > > Given the docs you quoted, no --- I was just too lazy to look up whether > it set is_called or not. With the +1 you don't have to think ;-) > > Even less thinking: CREATE OR REPLACE FUNCTION public.set_sequence(name, int4) RETURNS int4 AS ' DECLARE l_sequence_name ALIAS FOR $1; l_last_value ALIAS FOR $2; BEGIN IF l_last_value = 0 THEN PERFORM setval(l_sequence_name,1, False); ELSE PERFORM setval(l_sequence_name,l_last_value); END IF; RETURN 1; END;' LANGUAGE 'plpgsql' VOLATILE; Regards, Berend Tober