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/'); 104create index test2_idx_gin on test2 using gin (t gin_trgm_ops); 105set enable_seqscan=off; 106explain (costs off) 107 select * from test2 where t like '%BCD%'; 108explain (costs off) 109 select * from test2 where t ilike '%BCD%'; 110select * from test2 where t like '%BCD%'; 111select * from test2 where t like '%bcd%'; 112select * from test2 where t like E'%\\bcd%'; 113select * from test2 where t ilike '%BCD%'; 114select * from test2 where t ilike 'qua%'; 115select * from test2 where t like '%z foo bar%'; 116select * from test2 where t like ' z foo%'; 117explain (costs off) 118 select * from test2 where t ~ '[abc]{3}'; 119explain (costs off) 120 select * from test2 where t ~* 'DEF'; 121select * from test2 where t ~ '[abc]{3}'; 122select * from test2 where t ~ 'a[bc]+d'; 123select * from test2 where t ~ '(abc)*$'; 124select * from test2 where t ~* 'DEF'; 125select * from test2 where t ~ 'dEf'; 126select * from test2 where t ~* '^q'; 127select * from test2 where t ~* '[abc]{3}[def]{3}'; 128select * from test2 where t ~* 'ab[a-z]{3}'; 129select * from test2 where t ~* '(^| )qua'; 130select * from test2 where t ~ 'q.*rk$'; 131select * from test2 where t ~ 'q'; 132select * from test2 where t ~ '[a-z]{3}'; 133select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}'; 134select * from test2 where t ~ 'z foo bar'; 135select * from test2 where t ~ ' z foo bar'; 136select * from test2 where t ~ ' z foo bar'; 137select * from test2 where t ~ ' z foo'; 138select * from test2 where t ~ 'qua(?!foo)'; 139select * from test2 where t ~ '/\d+/-\d'; 140drop index test2_idx_gin; 141 142create index test2_idx_gist on test2 using gist (t gist_trgm_ops); 143set enable_seqscan=off; 144explain (costs off) 145 select * from test2 where t like '%BCD%'; 146explain (costs off) 147 select * from test2 where t ilike '%BCD%'; 148select * from test2 where t like '%BCD%'; 149select * from test2 where t like '%bcd%'; 150select * from test2 where t like E'%\\bcd%'; 151select * from test2 where t ilike '%BCD%'; 152select * from test2 where t ilike 'qua%'; 153select * from test2 where t like '%z foo bar%'; 154select * from test2 where t like ' z foo%'; 155explain (costs off) 156 select * from test2 where t ~ '[abc]{3}'; 157explain (costs off) 158 select * from test2 where t ~* 'DEF'; 159select * from test2 where t ~ '[abc]{3}'; 160select * from test2 where t ~ 'a[bc]+d'; 161select * from test2 where t ~ '(abc)*$'; 162select * from test2 where t ~* 'DEF'; 163select * from test2 where t ~ 'dEf'; 164select * from test2 where t ~* '^q'; 165select * from test2 where t ~* '[abc]{3}[def]{3}'; 166select * from test2 where t ~* 'ab[a-z]{3}'; 167select * from test2 where t ~* '(^| )qua'; 168select * from test2 where t ~ 'q.*rk$'; 169select * from test2 where t ~ 'q'; 170select * from test2 where t ~ '[a-z]{3}'; 171select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}'; 172select * from test2 where t ~ 'z foo bar'; 173select * from test2 where t ~ ' z foo bar'; 174select * from test2 where t ~ ' z foo bar'; 175select * from test2 where t ~ ' z foo'; 176select * from test2 where t ~ 'qua(?!foo)'; 177select * from test2 where t ~ '/\d+/-\d'; 178 179-- Check similarity threshold (bug #14202) 180 181CREATE TEMP TABLE restaurants (city text); 182INSERT INTO restaurants SELECT 'Warsaw' FROM generate_series(1, 10000); 183INSERT INTO restaurants SELECT 'Szczecin' FROM generate_series(1, 10000); 184CREATE INDEX ON restaurants USING gist(city gist_trgm_ops); 185 186-- Similarity of the two names (for reference). 187SELECT similarity('Szczecin', 'Warsaw'); 188 189-- Should get only 'Warsaw' for either setting of set_limit. 190EXPLAIN (COSTS OFF) 191SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit() 192 FROM restaurants WHERE city % 'Warsaw'; 193SELECT set_limit(0.3); 194SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit() 195 FROM restaurants WHERE city % 'Warsaw'; 196SELECT set_limit(0.5); 197SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit() 198 FROM restaurants WHERE city % 'Warsaw'; 199