1CREATE EXTENSION pg_trgm;
2
3-- Check whether any of our opclasses fail amvalidate
4SELECT amname, opcname
5FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
6WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
7
8--backslash is used in tests below, installcheck will fail if
9--standard_conforming_string is off
10set standard_conforming_strings=on;
11
12-- reduce noise
13set extra_float_digits = 0;
14
15select show_trgm('');
16select show_trgm('(*&^$@%@');
17select show_trgm('a b c');
18select show_trgm(' a b c ');
19select show_trgm('aA bB cC');
20select show_trgm(' aA bB cC ');
21select show_trgm('a b C0*%^');
22
23select similarity('wow','WOWa ');
24select similarity('wow',' WOW ');
25
26select similarity('---', '####---');
27
28CREATE TABLE test_trgm(t text COLLATE "C");
29
30\copy test_trgm from 'data/trgm.data'
31
32select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
33select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
34select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
35select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
36select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
37
38create index trgm_idx on test_trgm using gist (t gist_trgm_ops);
39set enable_seqscan=off;
40
41select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
42select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
43select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
44explain (costs off)
45select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
46select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
47select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
48
49drop index trgm_idx;
50create index trgm_idx on test_trgm using gist (t gist_trgm_ops(siglen=0));
51create index trgm_idx on test_trgm using gist (t gist_trgm_ops(siglen=2025));
52create index trgm_idx on test_trgm using gist (t gist_trgm_ops(siglen=2024));
53set enable_seqscan=off;
54
55select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
56select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
57select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
58explain (costs off)
59select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
60select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
61select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
62
63drop index trgm_idx;
64create index trgm_idx on test_trgm using gin (t gin_trgm_ops);
65set enable_seqscan=off;
66
67select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
68select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
69select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
70select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
71
72-- check handling of indexquals that generate no searchable conditions
73explain (costs off)
74select count(*) from test_trgm where t like '%99%' and t like '%qwerty%';
75select count(*) from test_trgm where t like '%99%' and t like '%qwerty%';
76explain (costs off)
77select count(*) from test_trgm where t like '%99%' and t like '%qw%';
78select count(*) from test_trgm where t like '%99%' and t like '%qw%';
79-- ensure that pending-list items are handled correctly, too
80create temp table t_test_trgm(t text COLLATE "C");
81create index t_trgm_idx on t_test_trgm using gin (t gin_trgm_ops);
82insert into t_test_trgm values ('qwerty99'), ('qwerty01');
83explain (costs off)
84select count(*) from t_test_trgm where t like '%99%' and t like '%qwerty%';
85select count(*) from t_test_trgm where t like '%99%' and t like '%qwerty%';
86explain (costs off)
87select count(*) from t_test_trgm where t like '%99%' and t like '%qw%';
88select count(*) from t_test_trgm where t like '%99%' and t like '%qw%';
89
90-- run the same queries with sequential scan to check the results
91set enable_bitmapscan=off;
92set enable_seqscan=on;
93select count(*) from test_trgm where t like '%99%' and t like '%qwerty%';
94select count(*) from test_trgm where t like '%99%' and t like '%qw%';
95select count(*) from t_test_trgm where t like '%99%' and t like '%qwerty%';
96select count(*) from t_test_trgm where t like '%99%' and t like '%qw%';
97reset enable_bitmapscan;
98
99create table test2(t text COLLATE "C");
100insert into test2 values ('abcdef');
101insert into test2 values ('quark');
102insert into test2 values ('  z foo bar');
103insert into test2 values ('/123/-45/');
104insert into test2 values ('line 1');
105insert into test2 values ('%line 2');
106insert into test2 values ('line 3%');
107insert into test2 values ('%line 4%');
108insert into test2 values ('%li%ne 5%');
109insert into test2 values ('li_e 6');
110create index test2_idx_gin on test2 using gin (t gin_trgm_ops);
111set enable_seqscan=off;
112explain (costs off)
113  select * from test2 where t like '%BCD%';
114explain (costs off)
115  select * from test2 where t ilike '%BCD%';
116select * from test2 where t like '%BCD%';
117select * from test2 where t like '%bcd%';
118select * from test2 where t like E'%\\bcd%';
119select * from test2 where t ilike '%BCD%';
120select * from test2 where t ilike 'qua%';
121select * from test2 where t like '%z foo bar%';
122select * from test2 where t like '  z foo%';
123explain (costs off)
124  select * from test2 where t ~ '[abc]{3}';
125explain (costs off)
126  select * from test2 where t ~* 'DEF';
127select * from test2 where t ~ '[abc]{3}';
128select * from test2 where t ~ 'a[bc]+d';
129select * from test2 where t ~ '(abc)*$';
130select * from test2 where t ~* 'DEF';
131select * from test2 where t ~ 'dEf';
132select * from test2 where t ~* '^q';
133select * from test2 where t ~* '[abc]{3}[def]{3}';
134select * from test2 where t ~* 'ab[a-z]{3}';
135select * from test2 where t ~* '(^| )qua';
136select * from test2 where t ~ 'q.*rk$';
137select * from test2 where t ~ 'q';
138select * from test2 where t ~ '[a-z]{3}';
139select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
140select * from test2 where t ~ 'z foo bar';
141select * from test2 where t ~ ' z foo bar';
142select * from test2 where t ~ '  z foo bar';
143select * from test2 where t ~ '  z foo';
144select * from test2 where t ~ 'qua(?!foo)';
145select * from test2 where t ~ '/\d+/-\d';
146-- test = operator
147explain (costs off)
148  select * from test2 where t = 'abcdef';
149select * from test2 where t = 'abcdef';
150explain (costs off)
151  select * from test2 where t = '%line%';
152select * from test2 where t = '%line%';
153select * from test2 where t = 'li_e 1';
154select * from test2 where t = '%line 2';
155select * from test2 where t = 'line 3%';
156select * from test2 where t = '%line 3%';
157select * from test2 where t = '%line 4%';
158select * from test2 where t = '%line 5%';
159select * from test2 where t = '%li_ne 5%';
160select * from test2 where t = '%li%ne 5%';
161select * from test2 where t = 'line 6';
162select * from test2 where t = 'li_e 6';
163drop index test2_idx_gin;
164
165create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
166set enable_seqscan=off;
167explain (costs off)
168  select * from test2 where t like '%BCD%';
169explain (costs off)
170  select * from test2 where t ilike '%BCD%';
171select * from test2 where t like '%BCD%';
172select * from test2 where t like '%bcd%';
173select * from test2 where t like E'%\\bcd%';
174select * from test2 where t ilike '%BCD%';
175select * from test2 where t ilike 'qua%';
176select * from test2 where t like '%z foo bar%';
177select * from test2 where t like '  z foo%';
178explain (costs off)
179  select * from test2 where t ~ '[abc]{3}';
180explain (costs off)
181  select * from test2 where t ~* 'DEF';
182select * from test2 where t ~ '[abc]{3}';
183select * from test2 where t ~ 'a[bc]+d';
184select * from test2 where t ~ '(abc)*$';
185select * from test2 where t ~* 'DEF';
186select * from test2 where t ~ 'dEf';
187select * from test2 where t ~* '^q';
188select * from test2 where t ~* '[abc]{3}[def]{3}';
189select * from test2 where t ~* 'ab[a-z]{3}';
190select * from test2 where t ~* '(^| )qua';
191select * from test2 where t ~ 'q.*rk$';
192select * from test2 where t ~ 'q';
193select * from test2 where t ~ '[a-z]{3}';
194select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
195select * from test2 where t ~ 'z foo bar';
196select * from test2 where t ~ ' z foo bar';
197select * from test2 where t ~ '  z foo bar';
198select * from test2 where t ~ '  z foo';
199select * from test2 where t ~ 'qua(?!foo)';
200select * from test2 where t ~ '/\d+/-\d';
201-- test = operator
202explain (costs off)
203  select * from test2 where t = 'abcdef';
204select * from test2 where t = 'abcdef';
205explain (costs off)
206  select * from test2 where t = '%line%';
207select * from test2 where t = '%line%';
208select * from test2 where t = 'li_e 1';
209select * from test2 where t = '%line 2';
210select * from test2 where t = 'line 3%';
211select * from test2 where t = '%line 3%';
212select * from test2 where t = '%line 4%';
213select * from test2 where t = '%line 5%';
214select * from test2 where t = '%li_ne 5%';
215select * from test2 where t = '%li%ne 5%';
216select * from test2 where t = 'line 6';
217select * from test2 where t = 'li_e 6';
218
219-- Check similarity threshold (bug #14202)
220
221CREATE TEMP TABLE restaurants (city text);
222INSERT INTO restaurants SELECT 'Warsaw' FROM generate_series(1, 10000);
223INSERT INTO restaurants SELECT 'Szczecin' FROM generate_series(1, 10000);
224CREATE INDEX ON restaurants USING gist(city gist_trgm_ops);
225
226-- Similarity of the two names (for reference).
227SELECT similarity('Szczecin', 'Warsaw');
228
229-- Should get only 'Warsaw' for either setting of set_limit.
230EXPLAIN (COSTS OFF)
231SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
232  FROM restaurants WHERE city % 'Warsaw';
233SELECT set_limit(0.3);
234SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
235  FROM restaurants WHERE city % 'Warsaw';
236SELECT set_limit(0.5);
237SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
238  FROM restaurants WHERE city % 'Warsaw';
239