forked from armollica/qcew-data
-
Notifications
You must be signed in to change notification settings - Fork 3
/
import_lookups.sh
executable file
·93 lines (68 loc) · 2.07 KB
/
import_lookups.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
# #!/bin/bash
source config.sh
# Area titles
query="
CREATE TABLE area_titles (
area_fips varchar,
area_title varchar
);
CREATE UNIQUE INDEX area_fips_lookup_index ON area_titles (area_fips);
COPY area_titles(area_fips, area_title)
FROM '$PATH/lookups/area_titles.csv' DELIMITER ',' CSV HEADER;
"
$PSQL -q $DATABASE -c "$query"
# Ownership codes
query="
CREATE TABLE ownership_titles (
own_code varchar,
own_title varchar
);
CREATE UNIQUE INDEX own_code_lookup_index ON ownership_titles (own_code);
COPY ownership_titles(own_code, own_title)
FROM '$PATH/lookups/ownership_titles.csv' DELIMITER ',' CSV HEADER;
"
$PSQL -q $DATABASE -c "$query"
# Industry titles
query="
CREATE TABLE industry_titles (
industry_code varchar,
industry_title varchar
);
CREATE UNIQUE INDEX industry_code_lookup_index ON industry_titles (industry_code);
COPY industry_titles(industry_code, industry_title)
FROM '$PATH/lookups/industry_titles.csv' DELIMITER ',' CSV HEADER;
"
$PSQL -q $DATABASE -c "$query"
# Aggregation level codes
query="
CREATE TABLE agglvl_titles (
agglvl_code varchar,
agglvl_title varchar
);
CREATE UNIQUE INDEX agglvl_code_lookup_index ON agglvl_titles (agglvl_code);
COPY agglvl_titles(agglvl_code, agglvl_title)
FROM '$PATH/lookups/agglevel_titles.csv' DELIMITER ',' CSV HEADER;
"
$PSQL -q $DATABASE -c "$query"
# Size codes
query="
CREATE TABLE size_titles (
own_code varchar,
own_title varchar
);
CREATE UNIQUE INDEX own_code_lookup_index ON size_titles (own_code);
COPY size_titles(own_code, own_title)
FROM '$PATH/lookups/size_titles.csv' DELIMITER ',' CSV HEADER;
"
$PSQL -q $DATABASE -c "$query"
# CPI
query="
CREATE TABLE annual_cpi (
year varchar,
cpi real
);
CREATE UNIQUE INDEX annual_cpi_lookup_index ON annual_cpi (year);
COPY annual_cpi(year, cpi)
FROM '$PATH/lookups/annual_cpi.csv' DELIMITER ',' CSV HEADER;
"
$PSQL -q $DATABASE -c "$query"