> --------------------
> drop table A;
> create table A (
> id SERIAL PRIMARY KEY,
> foo int default 5,
> bar int default 10
> );
>
> insert into A (id, foo, bar) values (1, 1, 1);
> insert into A (id, foo, bar) values (2, 2, 2);
> insert into A (id, foo, bar) values (3, 3, 3);
> insert into A (id, foo, bar) values (4, 4, 4);
A serial data type will allow you to input values into it, but the
counter is still at 0. That's why your first update statement's
nextval outputs "1". It's not showing what was already inserted, it's
showing what would have been. So at this point you need to set the
current value of id at 4.
> insert into A (foo, bar) values (5, 5);
> insert into A (foo, bar) values (6, 6);
> --------------------------
>
> The output that I get is:
>
> [ctindel@ct742301 Setup]$ p < a.sql
> DROP TABLE
> NOTICE: CREATE TABLE will create implicit sequence 'a_id_seq' for
> SERIAL
> column 'a.id'
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
> for table 'a'
> CREATE TABLE
> INSERT 44289 1
> INSERT 44290 1
> INSERT 44291 1
> INSERT 44292 1
> ERROR: Cannot insert a duplicate key into unique index a_pkey
Here it's trying to insert "1"
> ERROR: Cannot insert a duplicate key into unique index a_pkey
Here "2"
Nextval *sets* the sequence at N+1.
> INSERT 44319 1
> id | foo | bar
> ----+-----+-----
> 1 | 1 | 1
> (1 row)
>
> nextval
> ---------
> 1
=== These are unrelated.
>
Nextval is at 4 now, so the insert (foo,bar) works correctly.
> id | foo | bar
> ----+-----+-----
> 1 | 1 | 1
> 2 | 2 | 2
> 3 | 3 | 3
> 4 | 4 | 4
> 5 | 5 | 5
> (5 rows)
>
> nextval
> ---------
> 6
< == This increases it again, showing the skipping behavior you were
seeing.