Cannot insert dup id in pk

From: Scott Cain <cain(at)cshl(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Cannot insert dup id in pk
Date: 2003-07-15 13:00:25
Message-ID: 1058274025.1451.6.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message teknokrat 2003-07-15 13:26:16 help with troublesome query
Previous Message Jan Bernhardt 2003-07-15 12:44:38 Non-Blocking Locks (i.e. Oracle NOWAIT)