Thread: 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