Re: [GENERAL] Am I using the SERIAL type properly?

[Jeffrey Melloy]

--------------------
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:

[EMAIL PROTECTED] 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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match