Обсуждение: 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