Date: Sat, 28 Dec 2002 07:48:37 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: COPY fails but INSERT works
In-Reply-To: <200212281513.32057.gary.stainburn@ringways.co.uk>
Message-ID: <20021228074200.H26525-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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