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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
