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
8select show_trgm('');
9select show_trgm('(*&^$@%@');
10select show_trgm('a b c');
11select show_trgm(' a b c ');
12select show_trgm('aA bB cC');
13select show_trgm(' aA bB cC ');
14select show_trgm('a b C0*%^');
15
16select similarity('wow','WOWa ');
17select similarity('wow',' WOW ');
18
19select similarity('---', '####---');
20
21CREATE TABLE test_trgm(t text COLLATE "C");
22
23\copy test_trgm from 'data/trgm.data'
24
25select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
26select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
27select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
28select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
29select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
30
31create index trgm_idx on test_trgm using gist (t gist_trgm_ops);
32set enable_seqscan=off;
33
34select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
35select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
36select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
37explain (costs off)
38select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
39select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
40select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
41
42drop index trgm_idx;
43create index trgm_idx on test_trgm using gin (t gin_trgm_ops);
44set enable_seqscan=off;
45
46select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
47select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
48select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
49select count(*) from test_trgm where t ~ '[qwerty]{2}-?[qwerty]{2}';
50
51create table test2(t text COLLATE "C");
52insert into test2 values ('abcdef');
53insert into test2 values ('quark');
54insert into test2 values ('  z foo bar');
55insert into test2 values ('/123/-45/');
56create index test2_idx_gin on test2 using gin (t gin_trgm_ops);
57set enable_seqscan=off;
58explain (costs off)
59  select * from test2 where t like '%BCD%';
60explain (costs off)
61  select * from test2 where t ilike '%BCD%';
62select * from test2 where t like '%BCD%';
63select * from test2 where t like '%bcd%';
64select * from test2 where t like E'%\\bcd%';
65select * from test2 where t ilike '%BCD%';
66select * from test2 where t ilike 'qua%';
67select * from test2 where t like '%z foo bar%';
68select * from test2 where t like '  z foo%';
69explain (costs off)
70  select * from test2 where t ~ '[abc]{3}';
71explain (costs off)
72  select * from test2 where t ~* 'DEF';
73select * from test2 where t ~ '[abc]{3}';
74select * from test2 where t ~ 'a[bc]+d';
75select * from test2 where t ~ '(abc)*$';
76select * from test2 where t ~* 'DEF';
77select * from test2 where t ~ 'dEf';
78select * from test2 where t ~* '^q';
79select * from test2 where t ~* '[abc]{3}[def]{3}';
80select * from test2 where t ~* 'ab[a-z]{3}';
81select * from test2 where t ~* '(^| )qua';
82select * from test2 where t ~ 'q.*rk$';
83select * from test2 where t ~ 'q';
84select * from test2 where t ~ '[a-z]{3}';
85select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
86select * from test2 where t ~ 'z foo bar';
87select * from test2 where t ~ ' z foo bar';
88select * from test2 where t ~ '  z foo bar';
89select * from test2 where t ~ '  z foo';
90select * from test2 where t ~ 'qua(?!foo)';
91select * from test2 where t ~ '/\d+/-\d';
92drop index test2_idx_gin;
93
94create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
95set enable_seqscan=off;
96explain (costs off)
97  select * from test2 where t like '%BCD%';
98explain (costs off)
99  select * from test2 where t ilike '%BCD%';
100select * from test2 where t like '%BCD%';
101select * from test2 where t like '%bcd%';
102select * from test2 where t like E'%\\bcd%';
103select * from test2 where t ilike '%BCD%';
104select * from test2 where t ilike 'qua%';
105select * from test2 where t like '%z foo bar%';
106select * from test2 where t like '  z foo%';
107explain (costs off)
108  select * from test2 where t ~ '[abc]{3}';
109explain (costs off)
110  select * from test2 where t ~* 'DEF';
111select * from test2 where t ~ '[abc]{3}';
112select * from test2 where t ~ 'a[bc]+d';
113select * from test2 where t ~ '(abc)*$';
114select * from test2 where t ~* 'DEF';
115select * from test2 where t ~ 'dEf';
116select * from test2 where t ~* '^q';
117select * from test2 where t ~* '[abc]{3}[def]{3}';
118select * from test2 where t ~* 'ab[a-z]{3}';
119select * from test2 where t ~* '(^| )qua';
120select * from test2 where t ~ 'q.*rk$';
121select * from test2 where t ~ 'q';
122select * from test2 where t ~ '[a-z]{3}';
123select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
124select * from test2 where t ~ 'z foo bar';
125select * from test2 where t ~ ' z foo bar';
126select * from test2 where t ~ '  z foo bar';
127select * from test2 where t ~ '  z foo';
128select * from test2 where t ~ 'qua(?!foo)';
129select * from test2 where t ~ '/\d+/-\d';
130
131-- Check similarity threshold (bug #14202)
132
133CREATE TEMP TABLE restaurants (city text);
134INSERT INTO restaurants SELECT 'Warsaw' FROM generate_series(1, 10000);
135INSERT INTO restaurants SELECT 'Szczecin' FROM generate_series(1, 10000);
136CREATE INDEX ON restaurants USING gist(city gist_trgm_ops);
137
138-- Similarity of the two names (for reference).
139SELECT similarity('Szczecin', 'Warsaw');
140
141-- Should get only 'Warsaw' for either setting of set_limit.
142EXPLAIN (COSTS OFF)
143SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
144  FROM restaurants WHERE city % 'Warsaw';
145SELECT set_limit(0.3);
146SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
147  FROM restaurants WHERE city % 'Warsaw';
148SELECT set_limit(0.5);
149SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
150  FROM restaurants WHERE city % 'Warsaw';
151