nextval(), and serial updates

From: "Sampath, Krishna" <KSampath(at)ekmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: nextval(), and serial updates
Date: 2000-04-04 14:32:17
Message-ID: EDD4714513C8D2118B940090273D1A82065DA1@NJ01SNT11
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Normally, currval() points to the index sequence for the last inserted
record.

However, after I call
select nextval('i_seq'::text)
on the serial data tyep i_seq, the counter gets incremented by 1. The next
INSERT skips a number in the sequence. Is this by design? I was a bit
surprized because the currval() gets incremented also, and you no longer
point to the last inserted record.

puzzled,
krishna

concrete example:
create table t1 ( i serial, s varchar(20) ); -- create table
insert into t1 (s) values ('first line'); -- insert 1 record
select * from t1; -- gives (1
row)
select currval('t1_i_seq'); -- gives 1 as
currval .. ok
select nextval('t1_i_seq'); -- gives 2 as
nextval .. ok so far
select currval('t1_i_seq'); -- gives 2 as
currval ... should this be 1?
insert into t1 (s) values ('second line'); -- inserts second line,
serial id set to 3
select * from t1; -- (2 rows)
i is now 3, should be 2

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ross J. Reedstrom 2000-04-04 14:56:34 Re: SELECT speed with LIKE
Previous Message Lamar Owen 2000-04-04 13:56:54 Re: The "nl" RPMs.