Обсуждение: 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