-
Notifications
You must be signed in to change notification settings - Fork 170
/
insert.sql
56 lines (42 loc) · 1.76 KB
/
insert.sql
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
--
-- Testing insert on cstore_fdw tables.
--
CREATE FOREIGN TABLE test_insert_command (a int) SERVER cstore_server;
-- test single row inserts fail
select count(*) from test_insert_command;
insert into test_insert_command values(1);
select count(*) from test_insert_command;
insert into test_insert_command default values;
select count(*) from test_insert_command;
-- test inserting from another table succeed
CREATE TABLE test_insert_command_data (a int);
select count(*) from test_insert_command_data;
insert into test_insert_command_data values(1);
select count(*) from test_insert_command_data;
insert into test_insert_command select * from test_insert_command_data;
select count(*) from test_insert_command;
drop table test_insert_command_data;
drop foreign table test_insert_command;
-- test long attribute value insertion
-- create sufficiently long text so that data is stored in toast
CREATE TABLE test_long_text AS
SELECT a as int_val, string_agg(random()::text, '') as text_val
FROM generate_series(1, 10) a, generate_series(1, 1000) b
GROUP BY a ORDER BY a;
-- store hash values of text for later comparison
CREATE TABLE test_long_text_hash AS
SELECT int_val, md5(text_val) AS hash
FROM test_long_text;
CREATE FOREIGN TABLE test_cstore_long_text(int_val int, text_val text)
SERVER cstore_server;
-- store long text in cstore table
INSERT INTO test_cstore_long_text SELECT * FROM test_long_text;
-- drop source table to remove original text from toast
DROP TABLE test_long_text;
-- check if text data is still available in cstore table
-- by comparing previously stored hash.
SELECT a.int_val
FROM test_long_text_hash a, test_cstore_long_text c
WHERE a.int_val = c.int_val AND a.hash = md5(c.text_val);
DROP TABLE test_long_text_hash;
DROP FOREIGN TABLE test_cstore_long_text;