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