Re: COPY fails but INSERT works - Mailing list pgsql-sql

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