Re: Cannot insert dup id in pk

I suspect the sequence is out of sync with the values actually in you
primary key (which I gues is fid.
Try this query
SELECT setval('public.fdata _fid_seq'::text,MAX(fid)+1) FROM fdata;
This should set the value of the sequence to the current maximum+1
hth,
- Stuart
P.S. Sorry about the format change the disclaimer adder forces

> -----Original Message-----
> From: Scott Cain [mailto:cain(at)cshl(dot)org]
> Sent: 15 July 2003 14:00
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] Cannot insert dup id in pk
>
>
> THIS EMAIL HAS BEEN SWEPT FOR VIRUSES BY THE NORTHCLIFFE
> GROUP MAILSWEEPER SERVER.
>
> 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,ftar
> get_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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

DISCLAIMER:The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.