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