Обсуждение: SERIAL type not autoincremented
Hello i have: create table student( id SERIAL NOT NULL, name VARCHAR(35) NOT NULL, primary key (id) ); and when i try to insert like this: insert into student (name) values('me'); i receive error: ERROR: duplicate key violates unique constraint "student_pkey" Why ? Do i understeand correctly that SERIAL == AUTO_INCREMENT ? As far as i remember i used such type in the past and had no problem. What do i miss ? Thanx Michal
teknet@poczta.onet.pl wrote: > > Hello > i have: > create table student( > id SERIAL NOT NULL, > name VARCHAR(35) NOT NULL, > primary key (id) > ); > > and when i try to insert like this: > insert into student (name) values('me'); > i receive error: > ERROR: duplicate key violates unique constraint "student_pkey" You must be leaving something out of the story... $ psql Password: Welcome to psql 7.4.2, the PostgreSQL interactive terminal. ... jseymour=# create table student( jseymour(# id SERIAL NOT NULL, jseymour(# name VARCHAR(35) NOT NULL, jseymour(# primary key (id) jseymour(# ); NOTICE: CREATE TABLE will create implicit sequence "student_id_seq" for "serial" column "student.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student" CREATE TABLE jseymour=# insert into student (name) values('me'); INSERT 8776502 1 jseymour=# insert into student (name) values('me'); INSERT 8776503 1 jseymour=# insert into student (name) values('me'); INSERT 8776504 1 jseymour=# select * from student; id | name ----+------ 1 | me 2 | me 3 | me (3 rows) Seems to work here. Jim
On Fri, Jul 02, 2004 at 19:22:17 +0200, teknet@poczta.onet.pl wrote: > Hello > i have: > create table student( > id SERIAL NOT NULL, > name VARCHAR(35) NOT NULL, > primary key (id) > ); > > and when i try to insert like this: > insert into student (name) values('me'); > i receive error: > ERROR: duplicate key violates unique constraint "student_pkey" > > Why ? Do i understeand correctly that SERIAL == AUTO_INCREMENT ? > As far as i remember i used such type in the past and had no problem. > What do i miss ? You probably manually changed the value of the sequence student_id_seq.
The serial datatype creates a sequence in the background. So, in your example, you'll have a sequence called "student_id_seq". Your sequence was probably reset, dropped/created, or whatever, to cause it to feed IDs back that already exist in your table. On July 2, 2004 10:22 am, teknet@poczta.onet.pl wrote: > Hello > i have: > create table student( > id SERIAL NOT NULL, > name VARCHAR(35) NOT NULL, > primary key (id) > ); > > and when i try to insert like this: > insert into student (name) values('me'); > i receive error: > ERROR: duplicate key violates unique constraint "student_pkey" > > Why ? Do i understeand correctly that SERIAL == AUTO_INCREMENT ? > As far as i remember i used such type in the past and had no problem. > What do i miss ? > > Thanx > Michal > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Michael A. Nachbaur <mike@nachbaur.com> http://nachbaur.com/pgpkey.asc
i found the problem:
sys=> create table test2(
sys(> id serial,
sys(> name varchar(10),
sys(> primary key(id)
sys(> );
NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for "serial" column "test2.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test2_pkey" for table "test2"
CREATE TABLE
sys=> insert into test2 values(1,'myname');
INSERT 18765 1
sys=> insert into test2 (name) values('myname2');
ERROR: duplicate key violates unique constraint "test2_pkey"
sys=>
Why is it so ?
Thanx
Michal
teknet@poczta.onet.pl wrote: > i found the problem: > > > > sys=> insert into test2 values(1,'myname'); > INSERT 18765 1 > sys=> insert into test2 (name) values('myname2'); > ERROR: duplicate key violates unique constraint "test2_pkey" > sys=> > > Why is it so ? > Because you explicitly put in 1: values(1,'myname') and the second insert values('myname2') which is short for values(default, 'myname2') gets the value for the ID column from the default (which is nextval('test2_id_seq')) and that's 1 too - there you go, unique constraint violation. Remember, this is _not_ mysql, where autoincrement columns are implemented(or so I hear) by select max(column_in_question) + 1. > > > Thanx > > Michal > Take care and do read/search the manual, it's quite good ! Regards, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243
On Fri, Jul 02, 2004 at 21:40:02 +0200, teknet@poczta.onet.pl wrote: > i found the problem: > > > > sys=> create table test2( > sys(> id serial, > sys(> name varchar(10), > sys(> primary key(id) > sys(> ); > NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for > "serial" column "test2.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test2_pkey" > for table "test2" > CREATE TABLE > sys=> insert into test2 values(1,'myname'); > INSERT 18765 1 > sys=> insert into test2 (name) values('myname2'); > ERROR: duplicate key violates unique constraint "test2_pkey" > sys=> > > Why is it so ? Because you are inserting records without using the sequence. The serial type is really a short cut for specifying that the default value is the value of a sequence created for that column. If you insert records without using the default, then you also need to set the value of the sequence higher than the largest value so far. You can use the setval function to do this.
teknet@poczta.onet.pl wrote: > i found the problem: > > > > sys=> create table test2( > sys(> id serial, > sys(> name varchar(10), > sys(> primary key(id) > sys(> ); > NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" > for "serial" column "test2.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "test2_pkey" for table "test2" > CREATE TABLE > sys=> insert into test2 values(1,'myname'); INSERT 18765 1 sys=> > insert into test2 (name) values('myname2'); ERROR: duplicate key > violates unique constraint "test2_pkey" > sys=> > > > Why is it so ? > > > > Thanx > > Michal Because you didn't let the serial column do it's magic. Try: Insert into test2(name) values('myname'); Insert into test2(name) values('myname2'); That should work. Always let a serial column pick the number. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Martha Stewart called it a Good Thing when teknet@poczta.onet.pl wrote: > Hello > i have: > create table student( > id SERIAL NOT NULL, > name VARCHAR(35) NOT NULL, > primary key (id) > ); > > and when i try to insert like this: > insert into student (name) values('me'); > i receive error: > ERROR: duplicate key violates unique constraint "student_pkey" > > Why ? Do i understeand correctly that SERIAL == AUTO_INCREMENT ? > As far as i remember i used such type in the past and had no problem. > What do i miss ? cbbrowne@wolfe:~$ psql osdb Welcome to psql 7.4.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit /* cbbrowne@[local]/wolfe osdb=*/ create table student( /*osdb(#*/id SERIAL NOT NULL, /*osdb(#*/name VARCHAR(35) NOT NULL, /*osdb(#*/primary key (id) /*osdb(#*/); NOTICE: CREATE TABLE will create implicit sequence "student_id_seq" for "serial" column "student.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student" CREATE TABLE /* cbbrowne@[local]/wolfe osdb=*/ insert into student (name) values('me'); INSERT 19423269 1 /* cbbrowne@[local]/wolfe osdb=*/ \q It sounds as though there's something more going on that you haven't told us about. What data was already in that table? If there wasn't anything already there, there could hardly be a duplicate. -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/lsf.html Academics denigrating "Popularizers" "During the rise of the merchant class, the landed aristocracy understood the value of creating food, but didn't appreciate that food isn't valuable unless it reaches hungry mouths. New ideas aren't valuable unless they reach hungry minds. " -- Mark Miller
В Птн, 02.07.2004, в 19:22, teknet@poczta.onet.pl пишет: > Hello > i have: > create table student( > id SERIAL NOT NULL, > name VARCHAR(35) NOT NULL, > primary key (id) > ); > > and when i try to insert like this: > insert into student (name) values('me'); > i receive error: > ERROR: duplicate key violates unique constraint "student_pkey" Is this scenario possible in standard SQL identity columns? From what I read here[1], a standard IDENTITY column can guarantee successful insertion of a unique key. Is there similar support planned for PostgreSQL? Thanks. -- Markus Bertheau <twanger@bluetwanger.de>
On Mon, Jul 05, 2004 at 01:42:35 +0200, Markus Bertheau <twanger@bluetwanger.de> wrote: > ?? ??????, 02.07.2004, ?? 19:22, teknet@poczta.onet.pl ??????????: > > Hello > > i have: > > create table student( > > id SERIAL NOT NULL, > > name VARCHAR(35) NOT NULL, > > primary key (id) > > ); > > > > and when i try to insert like this: > > insert into student (name) values('me'); > > i receive error: > > ERROR: duplicate key violates unique constraint "student_pkey" > > Is this scenario possible in standard SQL identity columns? From what I > read here[1], a standard IDENTITY column can guarantee successful > insertion of a unique key. Is there similar support planned for > PostgreSQL? I don't expect things are going to change. However it isn't a problem if you always use DEFAULT to generate the value to be inserted and that you use bigserial if you need more than 2 billion (2^31) values.
Larry Rosenman wrote: > teknet@poczta.onet.pl wrote: >> i found the problem: >> >> >> >> sys=> create table test2( >> sys(> id serial, >> sys(> name varchar(10), >> sys(> primary key(id) >> sys(> ); >> NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" >> for "serial" column "test2.id" >> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index >> "test2_pkey" for table "test2" >> CREATE TABLE >> sys=> insert into test2 values(1,'myname'); INSERT 18765 1 sys=> >> insert into test2 (name) values('myname2'); ERROR: duplicate key >> violates unique constraint "test2_pkey" >> sys=> >> >> >> Why is it so ? >> >> >> >> Thanx >> >> Michal > > Because you didn't let the serial column do it's magic. > > Try: > > Insert into test2(name) values('myname'); > Insert into test2(name) values('myname2'); > > That should work. > > Always let a serial column pick the number. Or, if you must specify the column, specify DEFAULT for the value: INSERT INTO test2 (id,name) values (DEFAULT,'myname3'); > > LER > >