Re: [SQL] Composite UNIQUE across two tables?
On 3/7/08, Jamie Tufnell <[EMAIL PROTECTED]> wrote: > On 3/7/08, Bart Degryse <[EMAIL PROTECTED]> wrote: > > I haven't tested these two statements, but I'm using exactly this > > concept on some tables myself. > > My equivalent of your users table contains some 3,000,000 records. > > My equivalent of your sites table contains some 150,000 records. > > And it works fine... > When I try to create the index I get the following error: > > ERROR: functions in index expression must be marked IMMUTABLE > > Out of curiosity, I declared it IMMUTABLE and it worked for the > purposes of my small, isolated test,. > > Am I opening myself up to problems by doing this? Does anyone have any comment on this? I've done some more testing and have yet to run into a problem. I'd be really interested to hear some opinions on this. Cheers, J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 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 = 2072group 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'),
valuetext,
iflagint,
outalts int,
sidate date,
istidint references stock,
primary key (scid, item))
--
Arthur R. Van Hook
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 629-0071 Cell
--
Arthur R. Van Hook
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 629-0071 Cell
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insert problem
On 3/9/08, A. R. Van Hook <[EMAIL PROTECTED]> 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
