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

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@postgresql.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