Thread: Insert problem
I have a table where some of the entries of should have been posted to the wrong cusid and item#: cusid | item# | 1747 | 149 | 9251 : Jonathon Krinke ....... 1747 | 150 | 10071 : Lindsey Steele ........ 1747 | 151 | 10187 : Lindsay Stapley ....... 1747 | 152 | 10188 : Amanda Goucher ........ 1888 | 0 | 10071 : PROM 75P1206 mi......... corrected: 1747 | 148 | 9250 : Jonathon Krinke ........ 1747 | 149 | 9251 : Jonathon Krinke ....... 1888 | 0 | 10071 : PROM 75P1206 mi...... 1888 | 1 | 10071 : Lindsey Steele 1888 | 2 | 10187 : Lindsay Stapley 1888 | 3 | 10188 : Amanda Goucher my correction script: The script worked fine untill I tried in on the following entries: 1841 | 2 | 9228 : Caty Case : SO:Bra:..... 1841 | 3 | 9621 : Kelsie Greenlee : PROM..... 2072 | 3 | null : Cookie Jared Cook :.......... 2072 | 4 | null : Cookie Jared Cook :........ 2072 | 5 | null : Cookie Jared Cook :........ insert into schItem (scid, value, iflag, outalts, sidate, istid) select 2072, i.value, i.iflag, i.outalts,i.sidate, i.istid from schItem i, schItem s where i.scid=1841 and i.item >= 3 and s.scid= 2072 group by i.value, i.iflag, i.outalts, i.sidate, i.istid, s.scid ERROR: duplicate key violates unique constraint "schitem_pkey" It seems I have something wrong with the select portion of the script but I can figure it out. Any idea?? thanks ps table defination create sequence schItem_item_seq create table schItem (scid int NOT NULL references schedule ON DELETE CASCADE, item intNOT NULL default nextval('schItem_item_seq'), value text, iflag int, outalts int, sidate date, istid int references stock, primary key (scid, item)) -- Arthur R. Van Hook hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 629-0071 Cell -- Arthur R. Van Hook hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 629-0071 Cell
On 3/9/08, A. R. Van Hook <hook@lota.us> wrote: > The script worked fine untill I tried in on the following entries: > > 1841 | 2 | 9228 : Caty Case : SO:Bra:..... > 1841 | 3 | 9621 : Kelsie Greenlee : PROM..... > > 2072 | 3 | null : Cookie Jared Cook :.......... > 2072 | 4 | null : Cookie Jared Cook :........ > 2072 | 5 | null : Cookie Jared Cook :........ > > insert into schItem (scid, value, iflag, outalts, sidate, istid) > > ERROR: duplicate key violates unique constraint "schitem_pkey" > > table defination > > create sequence schItem_item_seq > create table schItem > (scid int NOT NULL references schedule ON DELETE CASCADE, > item int NOT NULL default nextval('schItem_item_seq'), [snip] It looks like there's already a row where scid=2072 and item=nextval('schItem_item_seq'). Try: SELECT setval('schItem_item_seq', (SELECT max(item)+1 FROM schItem)); And then run your query again. Cheers, J.
At 09:20 AM 3/10/2008, pgsql-sql-owner@postgresql.org wrote: >Date: Mon, 10 Mar 2008 00:14:12 +0000 >From: "Jamie Tufnell" <diesql@googlemail.com> >To: pgsql-sql@postgresql.org >Subject: Re: Insert problem >Message-ID: ><b0a4f3350803091714n2b89425ev48410eb86451b973@mail.gmail.com> >[snip] > > table defination > > > > create sequence schItem_item_seq > > create table schItem > > (scid int NOT NULL references schedule ON DELETE CASCADE, > > item int NOT NULL default nextval('schItem_item_seq'), > >[snip] > >It looks like there's already a row where scid=2072 and >item=nextval('schItem_item_seq'). > >Try: > >SELECT setval('schItem_item_seq', (SELECT max(item)+1 FROM schItem)); > >And then run your query again. > >Cheers, >J. A friendly amendment to Jamie's (correct) advice. Be sure that no other tools are obtaining id's from the sequence or inserting rows into schItem when you run this.. (Safest way is to have the db offline when running this). I got some good info from the experts here about this while back and wrote up a little blog article detailing the issue (in short it's a big pain to do it while the db is online, with no obvious sure-fire solution): http://www.misuse.org/science/2007/08/07/obtaining-a-block-of-ids-from-a-sequence-in-postgresql/ I hope this helps a little! Steve