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

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