>i upgraded postgresql from 7.3 to 7.4 and noticed that SERIAL
>exists but does not working. Example:
>My table:
>create table1(
>id SERIAL,
>name VARCHAR(100)
>);
>
>insert into table1 (name) values('name1');
>ERROR: duplicate key violates unique constraint "table1_pkey"
>
>Why ? I want to have autoincrementation. I do not know (when i insert
>record) what id values should it have.
>How can i solve this problem ?
>
Is this really what you are doing? (Creating a new table with a SERIAL
column) or is this a table from a 7.3 dump file you are having trouble
restoring?
lee=# create table1(
lee(# id SERIAL,
lee(# name VARCHAR(100)
lee(# );
ERROR: syntax error at or near "table1" at character 8
lee=# create table table1(
lee(# id SERIAL,
lee(# name VARCHAR(100)
lee(# );
NOTICE: CREATE TABLE will create implicit sequence "table1_id_seq" for
"serial" column "table1.id"
CREATE TABLE
lee=# insert into table1 (name) values('name1');
INSERT 40912290 1
Notice how the SERIAL column automatically creates the sequence which
is used to generate the id values...
You can find out what that sequence is called if you are not sure ...
lee=# \d table1
Table "lee.table1"
Column | Type | Modifiers
--------+------------------------+-----------------------------------------------------
id | integer | not null default
nextval('lee.table1_id_seq'::text)
name | character varying(100) |
And if you already have a lot of values in that column, you can adjust
the sequence past the last one ...
lee=# select max(id) from table1;
max
-----
1
(1 row)
lee=# select setval('table1_id_seq', 2);
setval
--------
2
(1 row)
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail