-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdb-scripts.psql
157 lines (120 loc) · 7.72 KB
/
db-scripts.psql
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
-- Table: population
-- DROP TABLE IF EXISTS population;
CREATE TABLE IF NOT EXISTS population
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
premise_id text NOT NULL COLLATE pg_catalog."default",
total_animal_count bigint,
address text COLLATE pg_catalog."default",
city text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default",
postalcode bigint,
state text COLLATE pg_catalog."default",
lat numeric(10,5),
lon numeric(10,5),
CONSTRAINT pop_id PRIMARY KEY (id)
);
-- Table: movements
-- DROP TABLE IF EXISTS movements;
CREATE TABLE IF NOT EXISTS movements
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 11 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
company text COLLATE pg_catalog."default",
reason text COLLATE pg_catalog."default",
species text COLLATE pg_catalog."default",
moved_count bigint,
start_date date,
origin_premise_id text NOT NULL COLLATE pg_catalog."default",
dest_premise_id text NOT NULL COLLATE pg_catalog."default",
CONSTRAINT id PRIMARY KEY (id)
);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(1,'376DQUF',1000, '163, fake Street', 'Carthage', 'my_farm_name_B', 62321, 'IL', 42.04312, -94.73784);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(2,'088WKFT',1000, '677, fake Street', 'Edison', 'my_farm_name_C', 68936, 'NE', 41.99145, -103.34611);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(3,'225ZGAJ',1000, '154, fake Street', 'Bethany', 'my_farm_name_J', 64424, 'MO', 41.90538, -97.56184);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(4,'180UCBY',1000, '707, fake Street', 'Clay Center', 'my_farm_name_B', 68933, 'NE', 42.10786, -101.59227);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(5,'582PHBR',1000, '217, fake Street', 'Albion', 'my_farm_name_E', 46701, 'IN', 42.97002, -88.94877);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(6,'360FDCZ',1000, '308, fake Street', 'Hampton', 'my_farm_name_I', 50441, 'IA', 44.33265, -96.68334);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(7,'733DTME',1000, '026, fake Street', 'Shelby', 'my_farm_name_C', 51570, 'IA', 43.07102, -99.01941);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(8,'715ELOH',1000, '813, fake Street', 'Edison', 'my_farm_name_H', 68936, 'NE', 41.87143, -103.33981);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(9,'325UDIW',1000, '582, fake Street', 'Pittsfield', 'my_farm_name_B', 62363, 'IL', 41.0829, -94.3104);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(10,'671HMJV',1000, '501, fake Street', 'Leopold', 'my_farm_name_J', 47551, 'IN', 39.67177, -90.09549);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(11,'778EYUX',1000, '743, fake Street', 'Sheffield', 'my_farm_name_L', 50475, 'IA', 46.38174, -92.14026);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(12,'464KBIF',1000, '733, fake Street', 'Sac City', 'my_farm_name_R', 50583, 'IA', 46.07227, -93.91527);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(13,'477EDVX',1000, '028, fake Street', 'Battle Creek', 'my_farm_name_K', 51006, 'IA', 45.88508, -94.49498);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(14,'611ULSK',1000, '112, fake Street', 'Havelock', 'my_farm_name_M', 50574, 'IA', 46.36619, -93.63721);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(15,'027RDXY',1000, '012, fake Street', 'Wawaka', 'my_farm_name_S', 46794, 'IN', 45.03986, -84.37985);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(16,'220FAPG',1000, '834, fake Street', 'Fonda', 'my_farm_name_S', 50540, 'IA', 46.22264, -93.76849);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(17,'554DGRT',1000, '058, fake Street', 'Ute', 'my_farm_name_O', 51060, 'IA', 45.53324, -94.59197);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(18,'853OBOV',1000, '550, fake Street', 'Edison', 'my_farm_name_R', 68936, 'NE', 43.80099, -98.72475);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(19,'453YIAE',1000, '846, fake Street', 'Altona', 'my_farm_name_Q', 61414, 'IL', 44.67619, -89.01749);
INSERT INTO population(
id, premise_id, total_animal_count, address, city, name, postalcode, state, lat, lon)
VALUES(20,'730AOYL',1000, '613, fake Street', 'Flat Rock', 'my_farm_name_T', 62427, 'IL', 42.391528, -86.477042);
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (1,'Mypigcompany','FINISH TO FINISH','Swine','376DQUF','778EYUX',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (2,'Mypigcompany','SOW TO FINISH','Swine','088WKFT','464KBIF',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (3,'Mypigcompany','SOW TO NURSERY','Swine','225ZGAJ','477EDVX',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (4,'Mypigcompany','WTF TO FINISH','Swine','180UCBY','611ULSK',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (5,'Mypigcompany','WTF TO FINISH','Swine','582PHBR','027RDXY',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (6,'Mypigcompany','FINISH TO FINISH','Swine','360FDCZ','220FAPG',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (7,'Mypigcompany','WTF TO FINISH','Swine','733DTME','554DGRT',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (8,'Mypigcompany','SOW TO NURSERY','Swine','715ELOH','853OBOV',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (9,'Mypigcompany','SOW TO WTF','Swine','325UDIW','453YIAE',1000,TO_DATE('18-04-11', 'YY-MM-DD'));
INSERT INTO movements(
id, company, reason, species, origin_premise_id, dest_premise_id, moved_count, start_date)
VALUES (10,'Mypigcompany','SOW TO FINISH','Swine','671HMJV','730AOYL',1000,TO_DATE('18-04-11', 'YY-MM-DD'));