SERIAL type not autoincremented

[t***@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"

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

[Jim Seymour]

Post by t***@poczta.onet.pl
Hello
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
insert into student (name) values('me');
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

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

[Bruno Wolff III]

On Fri, Jul 02, 2004 at 19:22:17 +0200,

Post by t***@poczta.onet.pl
Hello
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
insert into student (name) values('me');
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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Michael A Nachbaur]

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.

Post by t***@poczta.onet.pl
Hello
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
insert into student (name) values('me');
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

[t***@poczta.onet.pl]

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

[Radu-Adrian Popescu]

Post by t***@poczta.onet.pl
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.

Post by t***@poczta.onet.pl
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.
[phone]


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Bruno Wolff III]

On Fri, Jul 02, 2004 at 21:40:02 +0200,

Post by t***@poczta.onet.pl
 
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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Larry Rosenman]

Post by t***@poczta.onet.pl
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

[Mike Rylander]

Post by Larry Rosenman

Post by t***@poczta.onet.pl
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.
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');

Post by Larry Rosenman
LER

[Christopher Browne]

Post by t***@poczta.onet.pl
Hello
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
insert into student (name) values('me');
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 ?

***@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

[Markus Bertheau]

Post by t***@poczta.onet.pl
Hello
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
insert into student (name) values('me');
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.

[Bruno Wolff III]

On Mon, Jul 05, 2004 at 01:42:35 +0200,

Post by Markus Bertheau

Post by t***@poczta.onet.pl
Hello
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
insert into student (name) values('me');
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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html