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