forked from jdaarevalo/docker_postgres_with_data
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathfill_tables.sql
More file actions
71 lines (62 loc) · 2.46 KB
/
Copy pathfill_tables.sql
File metadata and controls
71 lines (62 loc) · 2.46 KB
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
-- Set params
set session my.number_of_sales = '100';
set session my.number_of_users = '100';
set session my.number_of_products = '100';
set session my.number_of_stores = '100';
set session my.number_of_coutries = '100';
set session my.number_of_cities = '30';
set session my.status_names = '5';
set session my.start_date = '2019-01-01 00:00:00';
set session my.end_date = '2020-02-01 00:00:00';
-- load the pgcrypto extension to gen_random_uuid ()
CREATE EXTENSION pgcrypto;
-- Filling of products
INSERT INTO product
select id, concat('Product ', id)
FROM GENERATE_SERIES(1, current_setting('my.number_of_products')::int) as id;
-- Filling of countries
INSERT INTO country
select id, concat('Country ', id)
FROM GENERATE_SERIES(1, current_setting('my.number_of_coutries')::int) as id;
-- Filling of cities
INSERT INTO city
select id
, concat('City ', id)
, floor(random() * (current_setting('my.number_of_coutries')::int) + 1)::int
FROM GENERATE_SERIES(1, current_setting('my.number_of_cities')::int) as id;
-- Filling of stores
INSERT INTO store
select id
, concat('Store ', id)
, floor(random() * (current_setting('my.number_of_cities')::int) + 1)::int
FROM GENERATE_SERIES(1, current_setting('my.number_of_stores')::int) as id;
-- Filling of users
INSERT INTO users
select id
, concat('User ', id)
FROM GENERATE_SERIES(1, current_setting('my.number_of_users')::int) as id;
-- Filling of users
INSERT INTO status_name
select status_name_id
, concat('Status Name ', status_name_id)
FROM GENERATE_SERIES(1, current_setting('my.status_names')::int) as status_name_id;
-- Filling of sales
INSERT INTO sale
select gen_random_uuid ()
, round(CAST(float8 (random() * 10000) as numeric), 3)
, TO_TIMESTAMP(start_date, 'YYYY-MM-DD HH24:MI:SS') +
random()* (TO_TIMESTAMP(end_date, 'YYYY-MM-DD HH24:MI:SS')
- TO_TIMESTAMP(start_date, 'YYYY-MM-DD HH24:MI:SS'))
, floor(random() * (current_setting('my.number_of_products')::int) + 1)::int
, floor(random() * (current_setting('my.number_of_users')::int) + 1)::int
, floor(random() * (current_setting('my.number_of_stores')::int) + 1)::int
FROM GENERATE_SERIES(1, current_setting('my.number_of_sales')::int) as id
, current_setting('my.start_date') as start_date
, current_setting('my.end_date') as end_date;
-- Filling of order_status
INSERT INTO order_status
select gen_random_uuid ()
, date_sale + random()* (date_sale + '5 days' - date_sale)
, sale_id
, floor(random() * (current_setting('my.status_names')::int) + 1)::int
from sale;