pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert problem
3+ messages / 3 participants
[nested] [flat]
* Insert problem
@ 2008-03-09 21:02 A. R. Van Hook <hook@lota.us>
0 siblings, 1 reply; 3+ messages in thread
From: A. R. Van Hook @ 2008-03-09 21:02 UTC (permalink / raw)
To: pgsql-sql
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 int NOT 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Insert problem
@ 2008-03-10 00:14 Jamie Tufnell <diesql@googlemail.com>
parent: A. R. Van Hook <hook@lota.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jamie Tufnell @ 2008-03-10 00:14 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Insert problem
@ 2008-03-10 18:09 Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 3+ messages in thread
From: Steve Midgley @ 2008-03-10 18:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-03-10 18:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-09 21:02 Insert problem A. R. Van Hook <hook@lota.us>
2008-03-10 00:14 ` Jamie Tufnell <diesql@googlemail.com>
2008-03-10 18:09 Re: Insert problem Steve Midgley <public@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox