Re: resetting sequence to cur max value - Mailing list pgsql-general

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