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@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?

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