Thread: Cannot insert dup id in pk

Cannot insert dup id in pk

From
Scott Cain
Date:
Hello,

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
happen, 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) |
notnull default ''fstart        | integer                | not null default '0'fstop         | integer                |
notnull 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'::charactervarying))
 

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

-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         cain@cshl.org
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory