Re: COPY fails but INSERT works

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)?

>
> 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
> \.