From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: COPY fails but INSERT works
Date: Sat, 28 Dec 2002 15:13:31 +0000
Message-ID: <200212281513.32057.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: COPY fails but INSERT works
In-Reply-To: <200212281513.32057.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox