COPY fails but INSERT works - Mailing list pgsql-sql

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