forked from timescale/timescaledb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
merge.out
160 lines (151 loc) · 6.58 KB
/
merge.out
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
158
159
160
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
-- Create conditions table with location and temperature
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location SMALLINT NOT NULL,
temperature DOUBLE PRECISION NULL
);
SELECT create_hypertable(
'conditions',
'time',
chunk_time_interval => INTERVAL '5 seconds');
create_hypertable
-------------------------
(1,public,conditions,t)
(1 row)
INSERT INTO conditions
SELECT time, location, 14 as temperature
FROM generate_series(
'2021-01-01 00:00:00',
'2021-01-01 00:00:09',
INTERVAL '5 seconds'
) as time,
generate_series(1,4) as location;
-- Create conditions_updated table with location and temperature
CREATE TABLE conditions_updated (
time TIMESTAMPTZ NOT NULL,
location SMALLINT NOT NULL,
temperature DOUBLE PRECISION NULL
);
SELECT create_hypertable(
'conditions_updated',
'time',
chunk_time_interval => INTERVAL '5 seconds');
create_hypertable
---------------------------------
(2,public,conditions_updated,t)
(1 row)
-- Generate data that overlaps with conditions table
INSERT INTO conditions_updated
SELECT time, location, 80 as temperature
FROM generate_series(
'2021-01-01 00:00:05',
'2021-01-01 00:00:14',
INTERVAL '5 seconds'
) as time,
generate_series(1,4) as location;
-- Print table/rows/num of chunks
select * from conditions order by time, location asc;
time | location | temperature
------------------------------+----------+-------------
Fri Jan 01 00:00:00 2021 PST | 1 | 14
Fri Jan 01 00:00:00 2021 PST | 2 | 14
Fri Jan 01 00:00:00 2021 PST | 3 | 14
Fri Jan 01 00:00:00 2021 PST | 4 | 14
Fri Jan 01 00:00:05 2021 PST | 1 | 14
Fri Jan 01 00:00:05 2021 PST | 2 | 14
Fri Jan 01 00:00:05 2021 PST | 3 | 14
Fri Jan 01 00:00:05 2021 PST | 4 | 14
(8 rows)
select * from conditions_updated order by time, location asc;
time | location | temperature
------------------------------+----------+-------------
Fri Jan 01 00:00:05 2021 PST | 1 | 80
Fri Jan 01 00:00:05 2021 PST | 2 | 80
Fri Jan 01 00:00:05 2021 PST | 3 | 80
Fri Jan 01 00:00:05 2021 PST | 4 | 80
Fri Jan 01 00:00:10 2021 PST | 1 | 80
Fri Jan 01 00:00:10 2021 PST | 2 | 80
Fri Jan 01 00:00:10 2021 PST | 3 | 80
Fri Jan 01 00:00:10 2021 PST | 4 | 80
(8 rows)
select hypertable_name, count(*) as num_of_chunks from timescaledb_information.chunks group by hypertable_name;
hypertable_name | num_of_chunks
--------------------+---------------
conditions | 2
conditions_updated | 2
(2 rows)
-- Print expected values in the conditions table once conditions_updated is merged into it
-- If a key exists in both tables, we take average of the temperature measured
-- average logic here is a mess but it works
SELECT COALESCE(c.time, cu.time) as time,
COALESCE(c.location, cu.location) as location,
(COALESCE(c.temperature, cu.temperature) + COALESCE(cu.temperature, c.temperature))/2 as temperature
FROM conditions AS c FULL JOIN conditions_updated AS cu
ON c.time = cu.time AND c.location = cu.location;
time | location | temperature
------------------------------+----------+-------------
Fri Jan 01 00:00:00 2021 PST | 1 | 14
Fri Jan 01 00:00:00 2021 PST | 2 | 14
Fri Jan 01 00:00:00 2021 PST | 3 | 14
Fri Jan 01 00:00:00 2021 PST | 4 | 14
Fri Jan 01 00:00:05 2021 PST | 1 | 47
Fri Jan 01 00:00:05 2021 PST | 2 | 47
Fri Jan 01 00:00:05 2021 PST | 3 | 47
Fri Jan 01 00:00:05 2021 PST | 4 | 47
Fri Jan 01 00:00:10 2021 PST | 1 | 80
Fri Jan 01 00:00:10 2021 PST | 2 | 80
Fri Jan 01 00:00:10 2021 PST | 3 | 80
Fri Jan 01 00:00:10 2021 PST | 4 | 80
(12 rows)
-- Test that normal PostgreSQL tables can merge without exceptions
CREATE TABLE conditions_pg AS SELECT * FROM conditions;
CREATE TABLE conditions_updated_pg AS SELECT * FROM conditions_updated;
MERGE INTO conditions_pg c
USING conditions_updated_pg cu
ON c.time = cu.time AND c.location = cu.location
WHEN MATCHED THEN
UPDATE SET temperature = (c.temperature + cu.temperature)/2
WHEN NOT MATCHED THEN
INSERT (time, location, temperature) VALUES (cu.time, cu.location, cu.temperature);
SELECT * FROM conditions_pg ORDER BY time, location ASC;
time | location | temperature
------------------------------+----------+-------------
Fri Jan 01 00:00:00 2021 PST | 1 | 14
Fri Jan 01 00:00:00 2021 PST | 2 | 14
Fri Jan 01 00:00:00 2021 PST | 3 | 14
Fri Jan 01 00:00:00 2021 PST | 4 | 14
Fri Jan 01 00:00:05 2021 PST | 1 | 47
Fri Jan 01 00:00:05 2021 PST | 2 | 47
Fri Jan 01 00:00:05 2021 PST | 3 | 47
Fri Jan 01 00:00:05 2021 PST | 4 | 47
Fri Jan 01 00:00:10 2021 PST | 1 | 80
Fri Jan 01 00:00:10 2021 PST | 2 | 80
Fri Jan 01 00:00:10 2021 PST | 3 | 80
Fri Jan 01 00:00:10 2021 PST | 4 | 80
(12 rows)
-- Merge conditions_updated into conditions
\set ON_ERROR_STOP 0
MERGE INTO conditions c
USING conditions_updated cu
ON c.time = cu.time AND c.location = cu.location
WHEN MATCHED THEN
UPDATE SET temperature = (c.temperature + cu.temperature)/2
WHEN NOT MATCHED THEN
INSERT (time, location, temperature) VALUES (cu.time, cu.location, cu.temperature);
ERROR: The MERGE command does not support hypertables in this version
SELECT * FROM conditions ORDER BY time, location ASC;
time | location | temperature
------------------------------+----------+-------------
Fri Jan 01 00:00:00 2021 PST | 1 | 14
Fri Jan 01 00:00:00 2021 PST | 2 | 14
Fri Jan 01 00:00:00 2021 PST | 3 | 14
Fri Jan 01 00:00:00 2021 PST | 4 | 14
Fri Jan 01 00:00:05 2021 PST | 1 | 14
Fri Jan 01 00:00:05 2021 PST | 2 | 14
Fri Jan 01 00:00:05 2021 PST | 3 | 14
Fri Jan 01 00:00:05 2021 PST | 4 | 14
(8 rows)
\set ON_ERROR_STOP 1