PDA

View Full Version : Serial column problem

cutecode
07-07-02, 05:55
Why after expicitly inserting values into a serial collumn
my sequence for SERIAL column not updated ?
Here is my code:

CREATE TABLE tmp (id SERIAL, name varchar(100) NOT NULL);
insert into tmp values (1, '11111111111');
insert into tmp (name) values ('22222222');
select * from tmp;
drop table tmp;
drop sequence tmp_id_seq;



After executing this query:
insert into tmp (name) values ('22222222');

I get error:
'Cannot insert a duplicate key into unique index tmp_id_key'
syf
07-08-02, 13:32
because it should be nextval('tmp_id_seq')
eperich
07-08-02, 17:40
Let's examine these 2 rows

insert into tmp values (1, '11111111111');

Irt insert into field id the value 1
THE SEQUENCE IS NOT AFFECTED

insert into tmp (name) values ('22222222');

if you try this query
the sequence will be affected and try to
insert the following:

insert into tmp(id,name) values(1,'22222222');

if you have a serial do not explizit give a key or if you do so you must set teh sequence to this nuber otherwise this error will occur