Обсуждение: SERIAL type not autoincremented

Поиск
Список
Период
Сортировка

SERIAL type not autoincremented

От
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"

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

Re: SERIAL type not autoincremented

От
jseymour@linxnet.com (Jim Seymour)
Дата:
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

Re: SERIAL type not autoincremented

От
Bruno Wolff III
Дата:
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.

Re: SERIAL type not autoincremented

От
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.

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

Re: SERIAL type not autoincremented

От
teknet@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

Re: SERIAL type not autoincremented

От
Radu-Adrian Popescu
Дата:
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


Re: SERIAL type not autoincremented

От
Bruno Wolff III
Дата:
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.

Re: SERIAL type not autoincremented

От
"Larry Rosenman"
Дата:
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



Re: SERIAL type not autoincremented

От
Christopher Browne
Дата:
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

standard IDENTITY support (Was: Re: SERIAL type not autoincremented)

От
Markus Bertheau
Дата:
В Птн, 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>


Re: standard IDENTITY support (Was: Re: SERIAL type not autoincremented)

От
Bruno Wolff III
Дата:
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.

Re: SERIAL type not autoincremented

От
Mike Rylander
Дата:
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
>
>