Re: COPY fails but INSERT works

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(at)postgresql(dot)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