Re: resetting sequence to cur max value

On Tue, Dec 12, 2006 at 12:19:56PM -0500, Tom Lane wrote:
> developer(at)wexwarez(dot)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