[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] COPY fails but INSERT works
From:       Gary Stainburn <gary.stainburn () ringways ! co ! uk>
Date:       2002-12-28 17:23:39
[Download RAW message or body]

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     


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
[prev in list] [next in list] [prev in thread] [next in thread]