sequence problem - many rows
I have the following table
Column | Type | Modifiers
----------------+-------+-----------------------------------------------------------------
id | integer | not null default
nextval('cachedgroupmembers_id_seq'::regclass)
groupid | integer |
memberid | integer |
via | integer |
immediatepar | integer |
disabled | integer | not null default 0
Indexes:
"cachedgroupmembers_pkey" PRIMARY KEY, btree (id)
"cachedgroupmembers2" btree (memberid)
"cachedgroupmembers3" btree (groupid)
"disgroumem" btree (groupid, memberid, disabled)
And the sequence for the id column:
CREATE SEQUENCE cachedgroupmembers_id_seq CACHE 1000;
Now the table is empty and I try to insert a row in this table:
INSERT INTO cachedgroupmembers(groupid) values(55);
And it works.
Next I populate the table wittt aprox 700.000 records.
And I do again "INSERT INTO cachedgroupmembers(groupid) values(55);".
The error is : ERROR: duplicate key violates unique constraint
"cachedgroupmembers_pkey"
Thank you.
I shoul start the sequence with the last number in the higher number in
the table +1.
It works.
Sterpu Victor wrote:
Show quoted text
I have the following table
Column | Type | Modifiers
----------------+-------+-----------------------------------------------------------------id | integer | not null default
nextval('cachedgroupmembers_id_seq'::regclass)
groupid | integer |
memberid | integer |
via | integer |
immediatepar | integer |
disabled | integer | not null default 0
Indexes:
"cachedgroupmembers_pkey" PRIMARY KEY, btree (id)
"cachedgroupmembers2" btree (memberid)
"cachedgroupmembers3" btree (groupid)
"disgroumem" btree (groupid, memberid, disabled)And the sequence for the id column:
CREATE SEQUENCE cachedgroupmembers_id_seq CACHE 1000;Now the table is empty and I try to insert a row in this table:
INSERT INTO cachedgroupmembers(groupid) values(55);
And it works.Next I populate the table wittt aprox 700.000 records.
And I do again "INSERT INTO cachedgroupmembers(groupid) values(55);".
The error is : ERROR: duplicate key violates unique constraint
"cachedgroupmembers_pkey"Thank you.
On Tue, 2005-11-29 at 18:00 +0200, Sterpu Victor wrote:
I have the following table
...
And the sequence for the id column:
CREATE SEQUENCE cachedgroupmembers_id_seq CACHE 1000;Now the table is empty and I try to insert a row in this table:
INSERT INTO cachedgroupmembers(groupid) values(55);
And it works.Next I populate the table wittt aprox 700.000 records.
Now you need to set the sequence value:
SELECT setval('cachedgroupmembers_id_seq',(select max(id) FROM
cachedgroupmembers);
And I do again "INSERT INTO cachedgroupmembers(groupid) values(55);".
The error is : ERROR: duplicate key violates unique constraint
"cachedgroupmembers_pkey"
That was because the sequence was trying to reuse one of the 700,000
values you just loaded.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html