Thread: COPY fails but INSERT works
Hi folks,
I've got three tables (amongst others). The ranks table holds the various
ranks that people can hold within each department.
The jobtypes table holds the various jobs that need doing.
The abilities holds the relationship defining which ranks carry out which
jobs.
When I create the database, I use the COPY command. The ranks and jobtypes
are populated okay but the abilities table is empty. However, if I then
INSERT the data the inserts work fine.
Anyone got any ideas why?
create table ranks ( -- staff promotion ladders by dept.
rid int4 default nextval('ranks_rid_seq'::text) unique not null,
rdid character references depts(did), -- department
rrank int4 not null, -- departmental rank
rdesc character varying(40), -- Rank Description
constraint ranks_rank unique (rdid, rrank)
);
create unique index rk_index on ranks using btree ("rid", "rdid");
create table jobtypes ( -- jobs that require doing
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdid character references depts(did), -- Department ID
jdesc character varying(40) -- job description
);
create unique index jt_index on jobtypes using btree ("jid", "jdid");
create table abilities ( -- defines which jobtypes ranks are able for
ejid int4 not null, -- jobtype ID
edid character not null, -- dept ID
erid int4, -- rank ID
constraint c2 foreign key (ejid, edid) references jobtypes (jid, jdid),
constraint c2 foreign key (erid, edid) references ranks (rid, rdid)
);
copy "ranks" from stdin;
1 F 1 Cleaner
2 F 2 Passed Cleaner
3 F 3 Fireman
4 F 4 Passed Fireman
5 F 5 Diesel Driver
6 F 6 Driver
7 F 7 Inspector
8 O 1 Trainee TTI
9 O 2 Ticket Inspector
10 O 3 Trainee Guard
11 O 4 Guard
12 M 1 Volunteer
13 M 2 Apprentice
14 M 3 Fitter
15 M 4 Charge Fitter
16 M 5 Manager
17 A 1 Admin Staff
18 A 2 Roster Admin
19 A 3 Webmaster
20 S 1 Station Staff
21 S 2 Station Foreman
\.
copy "jobtypes" from stdin;
1 F Cleaner
2 F Ride-Out
3 F Fireman
4 F Driver
5 F Charge Cleaner
6 O Guard
8 M Duty Fitter
\.
copy "abilities" from stdin;
1 F 1
2 F 1
3 F 2
3 F 3
4 F 4
4 F 5
4 F 6
5 F 3
5 F 4
6 O 3
8 M 3
\.
nymr=# select count(*) from ranks;count
------- 21
(1 row)
nymr=# select count(*) from jobtypes;count
------- 7
(1 row)
nymr=# select count(*) from abilities;count
------- 0
(1 row)
nymr=# insert into abilities values (1, 'F', 1);
INSERT 404593 1
nymr=# select count(*) from abilities;count
------- 1
(1 row)
nymr=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Sat, 28 Dec 2002, Gary Stainburn wrote:
> When I create the database, I use the COPY command. The ranks and jobtypes
> are populated okay but the abilities table is empty. However, if I then
> INSERT the data the inserts work fine.
Do *all* of the inserts work? If any one of the rows fails I believe
the entire copy fails. Specifically, unless I miss something:
inserting into abilitiesejid edid erid 6 O 3
So looking for (6,'O') in jobtypes
and (3,'O') in ranks.
I don't see the latter row so you're violating the constraint I believe.
Are you sure you want to reference (rid, rdid) in ranks and not
(rrank, rdid)?
>
> Anyone got any ideas why?
>
> create table ranks ( -- staff promotion ladders by dept.
> rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> rdid character references depts(did), -- department
> rrank int4 not null, -- departmental rank
> rdesc character varying(40), -- Rank Description
> constraint ranks_rank unique (rdid, rrank)
> );
> create unique index rk_index on ranks using btree ("rid", "rdid");
>
> create table jobtypes ( -- jobs that require doing
> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> jdid character references depts(did), -- Department ID
> jdesc character varying(40) -- job description
> );
> create unique index jt_index on jobtypes using btree ("jid", "jdid");
>
> create table abilities ( -- defines which jobtypes ranks are able for
> ejid int4 not null, -- jobtype ID
> edid character not null, -- dept ID
> erid int4, -- rank ID
> constraint c2 foreign key (ejid, edid) references jobtypes (jid, jdid),
> constraint c2 foreign key (erid, edid) references ranks (rid, rdid)
> );
>
> copy "ranks" from stdin;
> 1 F 1 Cleaner
> 2 F 2 Passed Cleaner
> 3 F 3 Fireman
> 4 F 4 Passed Fireman
> 5 F 5 Diesel Driver
> 6 F 6 Driver
> 7 F 7 Inspector
> 8 O 1 Trainee TTI
> 9 O 2 Ticket Inspector
> 10 O 3 Trainee Guard
> 11 O 4 Guard
> 12 M 1 Volunteer
> 13 M 2 Apprentice
> 14 M 3 Fitter
> 15 M 4 Charge Fitter
> 16 M 5 Manager
> 17 A 1 Admin Staff
> 18 A 2 Roster Admin
> 19 A 3 Webmaster
> 20 S 1 Station Staff
> 21 S 2 Station Foreman
> \.
>
> copy "jobtypes" from stdin;
> 1 F Cleaner
> 2 F Ride-Out
> 3 F Fireman
> 4 F Driver
> 5 F Charge Cleaner
> 6 O Guard
> 8 M Duty Fitter
> \.
>
> copy "abilities" from stdin;
> 1 F 1
> 2 F 1
> 3 F 2
> 3 F 3
> 4 F 4
> 4 F 5
> 4 F 6
> 5 F 3
> 5 F 4
> 6 O 3
> 8 M 3
> \.
On Saturday 28 December 2002 3:48 pm, Stephan Szabo wrote:
> On Sat, 28 Dec 2002, Gary Stainburn wrote:
> > When I create the database, I use the COPY command. The ranks and
> > jobtypes are populated okay but the abilities table is empty. However,
> > if I then INSERT the data the inserts work fine.
>
> Do *all* of the inserts work? If any one of the rows fails I believe
> the entire copy fails. Specifically, unless I miss something:
>
> inserting into abilities
> ejid edid erid
> 6 O 3
>
> So looking for
> (6,'O') in jobtypes
> and
> (3,'O') in ranks.
>
> I don't see the latter row so you're violating the constraint I believe.
> Are you sure you want to reference (rid, rdid) in ranks and not
> (rrank, rdid)?
Hi Stephan,
Thanks for this. The constraint was correct but the data was wrong. I've
fixed it and it's now working.
Gary
>
> > Anyone got any ideas why?
> >
> > create table ranks ( -- staff promotion ladders by dept.
> > rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> > rdid character references depts(did), -- department
> > rrank int4 not null, -- departmental rank
> > rdesc character varying(40), -- Rank Description
> > constraint ranks_rank unique (rdid, rrank)
> > );
> > create unique index rk_index on ranks using btree ("rid", "rdid");
> >
> > create table jobtypes ( -- jobs that require doing
> > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > jdid character references depts(did), -- Department ID
> > jdesc character varying(40) -- job description
> > );
> > create unique index jt_index on jobtypes using btree ("jid", "jdid");
> >
> > create table abilities ( -- defines which jobtypes ranks are able for
> > ejid int4 not null, -- jobtype ID
> > edid character not null, -- dept ID
> > erid int4, -- rank ID
> > constraint c2 foreign key (ejid, edid) references jobtypes (jid, jdid),
> > constraint c2 foreign key (erid, edid) references ranks (rid, rdid)
> > );
> >
> > copy "ranks" from stdin;
> > 1 F 1 Cleaner
> > 2 F 2 Passed Cleaner
> > 3 F 3 Fireman
> > 4 F 4 Passed Fireman
> > 5 F 5 Diesel Driver
> > 6 F 6 Driver
> > 7 F 7 Inspector
> > 8 O 1 Trainee TTI
> > 9 O 2 Ticket Inspector
> > 10 O 3 Trainee Guard
> > 11 O 4 Guard
> > 12 M 1 Volunteer
> > 13 M 2 Apprentice
> > 14 M 3 Fitter
> > 15 M 4 Charge Fitter
> > 16 M 5 Manager
> > 17 A 1 Admin Staff
> > 18 A 2 Roster Admin
> > 19 A 3 Webmaster
> > 20 S 1 Station Staff
> > 21 S 2 Station Foreman
> > \.
> >
> > copy "jobtypes" from stdin;
> > 1 F Cleaner
> > 2 F Ride-Out
> > 3 F Fireman
> > 4 F Driver
> > 5 F Charge Cleaner
> > 6 O Guard
> > 8 M Duty Fitter
> > \.
> >
> > copy "abilities" from stdin;
> > 1 F 1
> > 2 F 1
> > 3 F 2
> > 3 F 3
> > 4 F 4
> > 4 F 5
> > 4 F 6
> > 5 F 3
> > 5 F 4
> > 6 O 3
> > 8 M 3
> > \.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000