Re: Cannot insert dup id in pk - Mailing list pgsql-sql

You must have your sequence out of date with the content of the table 
(so that the next value in the sequence has already been inserted).
One way to get into a situation like that is loading the table data with 
COPY (the input contains the pks, and the COPY command does not update 
the sequence, you have to do that manually after the copy is done).
Another way is simply inserting a row with an explicitly specified pkey:

insert into fdata (fid,...) values (100, ...);

Now, assuming, that you current sequence value is less then 100, and 
that the statement above succeedes (i.e., there is no fid=100 in the 
table yet), you'll get your sequence out of date. You'll still be able 
to use it, and insert the rows into the table *until* the current value 
reaches 100 - once that happens, an attempt to insert with the default 
fid will cause an error, because the sequence will generate a key, that 
already exists.

To fix this, you need to do something like:

select setval ('fdata_fid_seq', (select fid from fdata order by fid 
limit 1));

This will make sure that the next value your sequence generates is 
greater than any key that already exists in the table.

I hope, it helps...

Dima

insert into fdata

Scott Cain wrote:

>Hello,
>
>I sent this question yesterday morning, but it was not allowed because I
>wasn't subscribed to the list.  If it did make it through, I appologize
>for the dup.
>
>I am having strange behavior when I try to do an insert.  Postgres tells
>me that it cannot insert a duplicate key into a primary key index, when
>I am not trying to insert into it.  Can anyone shed light on why this is
>happening, or point out the error of my ways?
>
>Here are the details:
>wormbase=> \d fdata
>                                      Table "public.fdata"
>    Column     |          Type          |                       Modifiers
                                  
 
>---------------+------------------------+--------------------------------------- -----------------
> fid           | integer                | not null default nextval('public.fdata _fid_seq'::text)
> fref          | character varying(100) | not null default ''
> fstart        | integer                | not null default '0'
> fstop         | integer                | not null default '0'
> fbin          | double precision       | not null default '0.000000'
> ftypeid       | integer                | not null default '0'
> fscore        | double precision       |
> fstrand       | character varying(3)   |
> fphase        | character varying(3)   |
> gid           | integer                | not null default '0'
> ftarget_start | integer                |
> ftarget_stop  | integer                |
>Indexes: pk_fdata primary key btree (fid),
>         fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
>         fdata_ftypeid_idx btree (ftypeid),
>         fdata_gid_idx btree (gid)
>Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f strand = '-'::character varying))
>                   "chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase = '1'::character varying)) OR
(fphase= '2'::character varying))
 
>
>Now a chunk from my query log:
>Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG:  query: INSERT INTO fdata
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop)
>Jul 14 12:48:47 localhost postgres[2998]: [107-2]
VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL)
>Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR:  Cannot insert a duplicate key into unique index pk_fdata
>
>Note that I do not try to insert anything into fid, the primary key on
>this table.  Why does Postgres think I am?
>
>Thanks much,
>Scott
>
>  
>