/
index.out
92 lines (92 loc) · 2.96 KB
/
index.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
set client_min_messages to 'error';
drop extension if exists "parray_gin" cascade;
create extension "parray_gin";
set client_min_messages to 'warning';
\t on
\pset format unaligned
set enable_seqscan to off;
set client_min_messages to 'error';
drop table if exists test_table;
create table test_table(id bigserial, val text[]);
set client_min_messages to 'warning';
insert into test_table(val) values(array['foo1','bar1','baz1']);
insert into test_table(val) values(array['foo2','bar2','baz2']);
insert into test_table(val) values(array['foo3','bar3','baz3']);
insert into test_table(val) select val from test_table;
insert into test_table(val) select val from test_table;
insert into test_table(val) select val from test_table;
insert into test_table(val) values(array['foo4','bar4','baz4']);
insert into test_table(val) values(array['foo4','bar4','baz4']);
insert into test_table(val) values(array['foo4','bar4','baz4']);
insert into test_table(val) values(array['foo4','bar4one','baz4']);
insert into test_table(val) values(array['foo4','bar4two','baz4']);
insert into test_table(val) values(array['foo4','bar4three','baz4']);
insert into test_table(val) values(array['foo4','bar4four','baz4']);
insert into test_table(val) values(array['foo4','bar4fourty','baz4']);
-- 32
select count(*) from test_table;
32
drop index if exists test_val_idx;
create index test_val_idx on test_table using gin (val parray_gin_ops);
-- 8
select count(*) from test_table where val @> array['foo1','bar1','baz1'];
8
-- 8
select count(*) from test_table where val @@> array['foo1','bar1','baz1'];
8
-- 8
select count(*) from test_table where val @@> array['bar4%'];
8
-- 3
select count(*) from test_table where val @> array['bar4'];
3
-- 8
select count(*) from test_table where val @@> array['bar3%'];
8
-- 8
select count(*) from test_table where val @> array['bar3'];
8
-- 0
select count(*) from test_table where val @@> array['qux%'];
0
-- 0
select count(*) from test_table where val @@> array['qux%'];
0
-- 0
select count(*) from test_table where val @> array['%bar4%o%'];
0
-- 4
select count(*) from test_table where val @@> array['%bar4%o%'];
4
---- check that 12-sequences between % are not ignored when rechecked
-- 0
select count(*) from test_table where val @@> array['%bar4%Z%'];
0
---- todo fix
---- select count(*) from test_table where val @@> array[]::text[];
---- select count(*) from test_table where val @> array[]::text[];
---- todo fix
-- 0
select count(*) from test_table where val @> array['%'];
0
-- 0
select count(*) from test_table where val @@> array['%'];
32
-- 3
select count(*) from test_table where val <@ array['foo4', 'bar4', 'baz4'];
3
-- 3
select count(*) from test_table where val <@ array['foo4', 'bar4', 'baz4', 'qux'];
3
-- 32
select count(*) from test_table where val <@@ array['foo%', 'bar%', 'baz%'];
32
-- 2
select count(*) from test_table where val <@@ array['foo4', 'baz%', 'bar4%e'];
2
-- 0
select count(*) from test_table where val <@@ array['qux'];
0
set enable_seqscan to on;
\t off
\pset format aligned