-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcreate_voters_table
81 lines (81 loc) · 3.49 KB
/
create_voters_table
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
CREATE TABLE voters
(
id serial NOT NULL,
county_id varchar(8),
county_desc varchar(50),
voter_reg_num varchar(50),
status_cd varchar(8),
voter_status_desc varchar(50),
reason_cd varchar(8),
voter_status_reason_desc varchar(128),
absent_ind varchar(8),
name_prefx_cd varchar(16),
last_name varchar(64),
first_name varchar(64),
middle_name varchar(64),
name_suffix_lbl varchar(16),
res_street_address varchar(128),
res_city_desc varchar(128),
state_cd varchar(8),
zip_code varchar(16),
mail_addr1 varchar(128),
mail_addr2 varchar(128),
mail_addr3 varchar(128),
mail_addr4 varchar(128),
mail_city varchar(128),
mail_state varchar(8),
mail_zipcode varchar(16),
full_phone_number varchar(16),
race_code varchar(8),
ethnic_code varchar(8),
party_cd varchar(8),
gender_code varchar(8),
birth_age varchar(8),
birth_state varchar(8),
drivers_lic varchar(8),
registr_dt varchar(16),
precinct_abbrv varchar(8),
precinct_desc varchar(16),
municipality_abbrv varchar(16),
municipality_desc varchar(64),
ward_abbrv varchar(16),
ward_desc varchar(64),
cong_dist_abbrv varchar(64),
super_court_abbrv varchar(64),
judic_dist_abbrv varchar(64),
nc_senate_abbrv varchar(64),
nc_house_abbrv varchar(64),
county_commiss_abbrv varchar(64),
county_commiss_desc varchar(64),
township_abbrv varchar(64),
township_desc varchar(128),
school_dist_abbrv varchar(64),
school_dist_desc varchar(128),
fire_dist_abbrv varchar(64),
fire_dist_desc varchar(128),
water_dist_abbrv varchar(64),
water_dist_desc varchar(128),
sewer_dist_abbrv varchar(64),
sewer_dist_desc varchar(128),
sanit_dist_abbrv varchar(64),
sanit_dist_desc varchar(128),
rescue_dist_abbrv varchar(64),
rescue_dist_desc varchar(128),
munic_dist_abbrv varchar(64),
munic_dist_desc varchar(128),
dist_1_abbrv varchar(64),
dist_1_desc varchar(128),
dist_2_abbrv varchar(64),
dist_2_desc varchar(128),
confidential_ind varchar(64),
age varchar(64),
ncid varchar(64),
vtd_abbrv varchar(64),
vtd_desc varchar(128),
CONSTRAINT voter_pkey PRIMARY KEY (id)
);
CREATE INDEX ON voters ((lower(first_name)));
CREATE INDEX ON voters ((lower(middle_name)));
CREATE INDEX ON voters ((lower(last_name)));
CREATE INDEX ON voters ((lower(birth_age)));
\COPY voters(county_id, county_desc, voter_reg_num, status_cd, voter_status_desc, reason_cd, voter_status_reason_desc, absent_ind, name_prefx_cd, last_name, first_name, middle_name, name_suffix_lbl, res_street_address, res_city_desc, state_cd, zip_code, mail_addr1, mail_addr2, mail_addr3, mail_addr4, mail_city, mail_state, mail_zipcode, full_phone_number, race_code, ethnic_code, party_cd, gender_code, birth_age, birth_state, drivers_lic, registr_dt, precinct_abbrv, precinct_desc, municipality_abbrv, municipality_desc, ward_abbrv, ward_desc, cong_dist_abbrv, super_court_abbrv, judic_dist_abbrv, nc_senate_abbrv, nc_house_abbrv, county_commiss_abbrv, county_commiss_desc, township_abbrv, township_desc, school_dist_abbrv, school_dist_desc, fire_dist_abbrv, fire_dist_desc, water_dist_abbrv, water_dist_desc, sewer_dist_abbrv, sewer_dist_desc, sanit_dist_abbrv, sanit_dist_desc, rescue_dist_abbrv, rescue_dist_desc, munic_dist_abbrv, munic_dist_desc, dist_1_abbrv, dist_1_desc, dist_2_abbrv, dist_2_desc, confidential_ind, age, ncid, vtd_abbrv, vtd_desc) FROM '/Users/ericjackson/Dev/CfA/voting-hackathon/cfa-voting-api-2016/ncvoter11.utf' DELIMITER E'\t' CSV HEADER;