Re: Cannot insert dup id in pk - Mailing list pgsql-sql
| From | Henshall, Stuart - TNP Southwest |
|---|---|
| Subject | Re: Cannot insert dup id in pk |
| Date | |
| Msg-id | E382B5D8EDE1D6118DBE0008C759BCD601EAAC70@WCPEXCHANGE Whole thread Raw |
| In response to | Cannot insert dup id in pk (Scott Cain <cain@cshl.org>) |
| List | pgsql-sql |
<p><font size="2">I suspect the sequence is out of sync with the values actually in you primary key (which I gues is
fid.</font><br/><font size="2">Try this query</font><br /><font size="2">SELECT setval('public.fdata
_fid_seq'::text,MAX(fid)+1)FROM fdata;</font><br /><font size="2">This should set the value of the sequence to the
currentmaximum+1</font><br /><font size="2">hth,</font><br /><font size="2">- Stuart</font><br /><font size="2">P.S.
Sorryabout the format change the disclaimer adder forces</font><p><font size="2">> -----Original
Message-----</font><br/><font size="2">> From: Scott Cain [<a
href="mailto:cain@cshl.org">mailto:cain@cshl.org</a>]</font><br/><font size="2">> Sent: 15 July 2003 14:00</font><br
/><fontsize="2">> To: pgsql-sql@postgresql.org</font><br /><font size="2">> Subject: [SQL] Cannot insert dup id
inpk</font><br /><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">> THIS EMAIL HAS
BEENSWEPT FOR VIRUSES BY THE NORTHCLIFFE </font><br /><font size="2">> GROUP MAILSWEEPER SERVER.</font><br /><font
size="2">></font><br /><font size="2">> Hello,</font><br /><font size="2">> </font><br /><font size="2">> I
sentthis question yesterday morning, but it was not </font><br /><font size="2">> allowed because I</font><br
/><fontsize="2">> wasn't subscribed to the list. If it did make it through, I </font><br /><font size="2">>
appologize</font><br/><font size="2">> for the dup.</font><br /><font size="2">> </font><br /><font size="2">>
Iam having strange behavior when I try to do an insert. </font><br /><font size="2">> Postgres tells</font><br
/><fontsize="2">> me that it cannot insert a duplicate key into a primary key </font><br /><font size="2">>
index,when</font><br /><font size="2">> I am not trying to insert into it. Can anyone shed light on </font><br
/><fontsize="2">> why this is</font><br /><font size="2">> happening, or point out the error of my
ways?</font><br/><font size="2">> </font><br /><font size="2">> Here are the details:</font><br /><font
size="2">>wormbase=> \d fdata</font><br /><font size="2">> Table
"public.fdata"</font><br/><font size="2">> Column | Type | </font><br
/><fontsize="2">> Modifiers </font><br /><font
size="2">> </font><br /><font size="2">>
---------------+------------------------+---------------------</font><br/><font size="2">> ------------------
-----------------</font><br/><font size="2">> fid | integer | not null default </font><br
/><fontsize="2">> nextval('public.fdata _fid_seq'::text)</font><br /><font size="2">> fref | character
varying(100)| not null default ''</font><br /><font size="2">> fstart | integer | not null
default'0'</font><br /><font size="2">> fstop | integer | not null default '0'</font><br
/><fontsize="2">> fbin | double precision | not null default '0.000000'</font><br /><font
size="2">> ftypeid | integer | not null default '0'</font><br /><font size="2">>
fscore | double precision |</font><br /><font size="2">> fstrand | character varying(3)
|</font><br/><font size="2">> fphase | character varying(3) |</font><br /><font size="2">>
gid | integer | not null default '0'</font><br /><font size="2">> ftarget_start |
integer |</font><br /><font size="2">> ftarget_stop | integer |</font><br /><font
size="2">>Indexes: pk_fdata primary key btree (fid),</font><br /><font size="2">> fdata_fref_idx btree
(fref,fbin, fstart, fstop, </font><br /><font size="2">> ftypeid, gid),</font><br /><font size="2">>
fdata_ftypeid_idxbtree (ftypeid),</font><br /><font size="2">> fdata_gid_idx btree (gid)</font><br /><font
size="2">>Check constraints: "chk_fdata_fstrand" ((fstrand = </font><br /><font size="2">> '+'::character
varying)OR (f strand = '-'::character varying))</font><br /><font size="2">> "chk_fdata_fphase"
(((fphase= </font><br /><font size="2">> '0'::character varying) OR (fp hase = '1'::character </font><br /><font
size="2">>varying)) OR (fphase = '2'::character varying))</font><br /><font size="2">> </font><br /><font
size="2">>Now a chunk from my query log:</font><br /><font size="2">> Jul 14 12:48:47 localhost postgres[2998]:
[107-1]LOG: </font><br /><font size="2">> query: INSERT INTO fdata </font><br /><font size="2">>
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftar</font><br/><font size="2">>
get_start,ftarget_stop)</font><br/><font size="2">> Jul 14 12:48:47 localhost postgres[2998]: [107-2] </font><br
/><fontsize="2">> VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'</font><br /><font size="2">>
12358',NULL,NULL)</font><br/><font size="2">> Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: </font><br
/><fontsize="2">> Cannot insert a duplicate key into unique index pk_fdata</font><br /><font size="2">>
</font><br/><font size="2">> Note that I do not try to insert anything into fid, the primary key on</font><br
/><fontsize="2">> this table. Why does Postgres think I am?</font><br /><font size="2">> </font><br /><font
size="2">>Thanks much,</font><br /><font size="2">> Scott</font><br /><font size="2">> </font><br /><font
size="2">>-- </font><br /><font size="2">>
--------------------------------------------------------------</font><br/><font size="2">> ----------</font><br
/><fontsize="2">> Scott Cain, Ph. D. </font><br /><font size="2">>
cain@cshl.org</font><br/><font size="2">> GMOD Coordinator (<a href="http://www.gmod.org/"
target="_blank">http://www.gmod.org/</a>) </font><br /><font size="2">> 216-392-3087</font><br
/><fontsize="2">> Cold Spring Harbor Laboratory</font><br /><font size="2">> </font><br /><font size="2">>
</font><br/><font size="2">> ---------------------------(end of </font><br /><font size="2">>
broadcast)---------------------------</font><br/><font size="2">> TIP 5: Have you checked our extensive
FAQ?</font><br/><font size="2">> </font><br /><font size="2">> <a
href="http://www.postgresql.org/docs/faqs/FAQ.html"
target="_blank">http://www.postgresql.org/docs/faqs/FAQ.html</a></font><br/><font size="2">> </font><br
/><p><b><i><fontsize="2">DISCLAIMER:</font></i><font size="2">The information in this message is confidential and may
belegally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised.
Ifyou are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or
omissiontaken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if
youhave received this message in error. Thank you.</font></b><br />