1 CREATE EXTENSION pg_trgm;
8 select show_trgm('(*&^$@%@');
14 select show_trgm('a b c');
16 ---------------------------------------
17 {" a"," b"," c"," a "," b "," c "}
20 select show_trgm(' a b c ');
22 ---------------------------------------
23 {" a"," b"," c"," a "," b "," c "}
26 select show_trgm('aA bB cC');
28 ---------------------------------------------------------
29 {" a"," b"," c"," aa"," bb"," cc","aa ","bb ","cc "}
32 select show_trgm(' aA bB cC ');
34 ---------------------------------------------------------
35 {" a"," b"," c"," aa"," bb"," cc","aa ","bb ","cc "}
38 select show_trgm('a b C0*%^');
40 ---------------------------------------------
41 {" a"," b"," c"," a "," b "," c0","c0 "}
44 select similarity('wow','WOWa ');
50 select similarity('wow',' WOW ');
56 select similarity('---', '####---');
62 CREATE TABLE test_trgm(t text COLLATE "C");
63 \copy test_trgm from 'data/trgm.data'
64 select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
66 -------------+----------
68 qwertyu0980 | 0.714286
69 qwertyu0981 | 0.714286
70 qwertyu0982 | 0.714286
71 qwertyu0983 | 0.714286
72 qwertyu0984 | 0.714286
73 qwertyu0985 | 0.714286
74 qwertyu0986 | 0.714286
75 qwertyu0987 | 0.714286
76 qwertyu0989 | 0.714286
1066 qwertyu1000 | 0.411765
1069 select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
1071 -------------+----------
1073 qwertyu0980 | 0.411765
1074 qwertyu0981 | 0.411765
1075 qwertyu0982 | 0.411765
1076 qwertyu0983 | 0.411765
1077 qwertyu0984 | 0.411765
1078 qwertyu0985 | 0.411765
1079 qwertyu0986 | 0.411765
1080 qwertyu0987 | 0.411765
1081 qwertyu0989 | 0.411765
1082 qwertyu0088 | 0.333333
1083 qwertyu0098 | 0.333333
1084 qwertyu0188 | 0.333333
1085 qwertyu0288 | 0.333333
1086 qwertyu0388 | 0.333333
1087 qwertyu0488 | 0.333333
1088 qwertyu0588 | 0.333333
1089 qwertyu0688 | 0.333333
1090 qwertyu0788 | 0.333333
1091 qwertyu0888 | 0.333333
1092 qwertyu0900 | 0.333333
1093 qwertyu0901 | 0.333333
1094 qwertyu0902 | 0.333333
1095 qwertyu0903 | 0.333333
1096 qwertyu0904 | 0.333333
1097 qwertyu0905 | 0.333333
1098 qwertyu0906 | 0.333333
1099 qwertyu0907 | 0.333333
1100 qwertyu0908 | 0.333333
1101 qwertyu0909 | 0.333333
1102 qwertyu0910 | 0.333333
1103 qwertyu0911 | 0.333333
1104 qwertyu0912 | 0.333333
1105 qwertyu0913 | 0.333333
1106 qwertyu0914 | 0.333333
1107 qwertyu0915 | 0.333333
1108 qwertyu0916 | 0.333333
1109 qwertyu0917 | 0.333333
1110 qwertyu0918 | 0.333333
1111 qwertyu0919 | 0.333333
1112 qwertyu0920 | 0.333333
1113 qwertyu0921 | 0.333333
1114 qwertyu0922 | 0.333333
1115 qwertyu0923 | 0.333333
1116 qwertyu0924 | 0.333333
1117 qwertyu0925 | 0.333333
1118 qwertyu0926 | 0.333333
1119 qwertyu0927 | 0.333333
1120 qwertyu0928 | 0.333333
1121 qwertyu0929 | 0.333333
1122 qwertyu0930 | 0.333333
1123 qwertyu0931 | 0.333333
1124 qwertyu0932 | 0.333333
1125 qwertyu0933 | 0.333333
1126 qwertyu0934 | 0.333333
1127 qwertyu0935 | 0.333333
1128 qwertyu0936 | 0.333333
1129 qwertyu0937 | 0.333333
1130 qwertyu0938 | 0.333333
1131 qwertyu0939 | 0.333333
1132 qwertyu0940 | 0.333333
1133 qwertyu0941 | 0.333333
1134 qwertyu0942 | 0.333333
1135 qwertyu0943 | 0.333333
1136 qwertyu0944 | 0.333333
1137 qwertyu0945 | 0.333333
1138 qwertyu0946 | 0.333333
1139 qwertyu0947 | 0.333333
1140 qwertyu0948 | 0.333333
1141 qwertyu0949 | 0.333333
1142 qwertyu0950 | 0.333333
1143 qwertyu0951 | 0.333333
1144 qwertyu0952 | 0.333333
1145 qwertyu0953 | 0.333333
1146 qwertyu0954 | 0.333333
1147 qwertyu0955 | 0.333333
1148 qwertyu0956 | 0.333333
1149 qwertyu0957 | 0.333333
1150 qwertyu0958 | 0.333333
1151 qwertyu0959 | 0.333333
1152 qwertyu0960 | 0.333333
1153 qwertyu0961 | 0.333333
1154 qwertyu0962 | 0.333333
1155 qwertyu0963 | 0.333333
1156 qwertyu0964 | 0.333333
1157 qwertyu0965 | 0.333333
1158 qwertyu0966 | 0.333333
1159 qwertyu0967 | 0.333333
1160 qwertyu0968 | 0.333333
1161 qwertyu0969 | 0.333333
1162 qwertyu0970 | 0.333333
1163 qwertyu0971 | 0.333333
1164 qwertyu0972 | 0.333333
1165 qwertyu0973 | 0.333333
1166 qwertyu0974 | 0.333333
1167 qwertyu0975 | 0.333333
1168 qwertyu0976 | 0.333333
1169 qwertyu0977 | 0.333333
1170 qwertyu0978 | 0.333333
1171 qwertyu0979 | 0.333333
1172 qwertyu0990 | 0.333333
1173 qwertyu0991 | 0.333333
1174 qwertyu0992 | 0.333333
1175 qwertyu0993 | 0.333333
1176 qwertyu0994 | 0.333333
1177 qwertyu0995 | 0.333333
1178 qwertyu0996 | 0.333333
1179 qwertyu0997 | 0.333333
1180 qwertyu0998 | 0.333333
1181 qwertyu0999 | 0.333333
1184 select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
1186 -------------+----------
1187 qwertyu0988 | 0.333333
1190 select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
1192 ----------+-------------
1193 0.411765 | qwertyu0988
1197 create index trgm_idx on test_trgm using gist (t gist_trgm_ops);
1198 set enable_seqscan=off;
1199 select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
1201 -------------+----------
1203 qwertyu0980 | 0.714286
1204 qwertyu0981 | 0.714286
1205 qwertyu0982 | 0.714286
1206 qwertyu0983 | 0.714286
1207 qwertyu0984 | 0.714286
1208 qwertyu0985 | 0.714286
1209 qwertyu0986 | 0.714286
1210 qwertyu0987 | 0.714286
1211 qwertyu0989 | 0.714286
2201 qwertyu1000 | 0.411765
2204 select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
2206 -------------+----------
2208 qwertyu0980 | 0.411765
2209 qwertyu0981 | 0.411765
2210 qwertyu0982 | 0.411765
2211 qwertyu0983 | 0.411765
2212 qwertyu0984 | 0.411765
2213 qwertyu0985 | 0.411765
2214 qwertyu0986 | 0.411765
2215 qwertyu0987 | 0.411765
2216 qwertyu0989 | 0.411765
2217 qwertyu0088 | 0.333333
2218 qwertyu0098 | 0.333333
2219 qwertyu0188 | 0.333333
2220 qwertyu0288 | 0.333333
2221 qwertyu0388 | 0.333333
2222 qwertyu0488 | 0.333333
2223 qwertyu0588 | 0.333333
2224 qwertyu0688 | 0.333333
2225 qwertyu0788 | 0.333333
2226 qwertyu0888 | 0.333333
2227 qwertyu0900 | 0.333333
2228 qwertyu0901 | 0.333333
2229 qwertyu0902 | 0.333333
2230 qwertyu0903 | 0.333333
2231 qwertyu0904 | 0.333333
2232 qwertyu0905 | 0.333333
2233 qwertyu0906 | 0.333333
2234 qwertyu0907 | 0.333333
2235 qwertyu0908 | 0.333333
2236 qwertyu0909 | 0.333333
2237 qwertyu0910 | 0.333333
2238 qwertyu0911 | 0.333333
2239 qwertyu0912 | 0.333333
2240 qwertyu0913 | 0.333333
2241 qwertyu0914 | 0.333333
2242 qwertyu0915 | 0.333333
2243 qwertyu0916 | 0.333333
2244 qwertyu0917 | 0.333333
2245 qwertyu0918 | 0.333333
2246 qwertyu0919 | 0.333333
2247 qwertyu0920 | 0.333333
2248 qwertyu0921 | 0.333333
2249 qwertyu0922 | 0.333333
2250 qwertyu0923 | 0.333333
2251 qwertyu0924 | 0.333333
2252 qwertyu0925 | 0.333333
2253 qwertyu0926 | 0.333333
2254 qwertyu0927 | 0.333333
2255 qwertyu0928 | 0.333333
2256 qwertyu0929 | 0.333333
2257 qwertyu0930 | 0.333333
2258 qwertyu0931 | 0.333333
2259 qwertyu0932 | 0.333333
2260 qwertyu0933 | 0.333333
2261 qwertyu0934 | 0.333333
2262 qwertyu0935 | 0.333333
2263 qwertyu0936 | 0.333333
2264 qwertyu0937 | 0.333333
2265 qwertyu0938 | 0.333333
2266 qwertyu0939 | 0.333333
2267 qwertyu0940 | 0.333333
2268 qwertyu0941 | 0.333333
2269 qwertyu0942 | 0.333333
2270 qwertyu0943 | 0.333333
2271 qwertyu0944 | 0.333333
2272 qwertyu0945 | 0.333333
2273 qwertyu0946 | 0.333333
2274 qwertyu0947 | 0.333333
2275 qwertyu0948 | 0.333333
2276 qwertyu0949 | 0.333333
2277 qwertyu0950 | 0.333333
2278 qwertyu0951 | 0.333333
2279 qwertyu0952 | 0.333333
2280 qwertyu0953 | 0.333333
2281 qwertyu0954 | 0.333333
2282 qwertyu0955 | 0.333333
2283 qwertyu0956 | 0.333333
2284 qwertyu0957 | 0.333333
2285 qwertyu0958 | 0.333333
2286 qwertyu0959 | 0.333333
2287 qwertyu0960 | 0.333333
2288 qwertyu0961 | 0.333333
2289 qwertyu0962 | 0.333333
2290 qwertyu0963 | 0.333333
2291 qwertyu0964 | 0.333333
2292 qwertyu0965 | 0.333333
2293 qwertyu0966 | 0.333333
2294 qwertyu0967 | 0.333333
2295 qwertyu0968 | 0.333333
2296 qwertyu0969 | 0.333333
2297 qwertyu0970 | 0.333333
2298 qwertyu0971 | 0.333333
2299 qwertyu0972 | 0.333333
2300 qwertyu0973 | 0.333333
2301 qwertyu0974 | 0.333333
2302 qwertyu0975 | 0.333333
2303 qwertyu0976 | 0.333333
2304 qwertyu0977 | 0.333333
2305 qwertyu0978 | 0.333333
2306 qwertyu0979 | 0.333333
2307 qwertyu0990 | 0.333333
2308 qwertyu0991 | 0.333333
2309 qwertyu0992 | 0.333333
2310 qwertyu0993 | 0.333333
2311 qwertyu0994 | 0.333333
2312 qwertyu0995 | 0.333333
2313 qwertyu0996 | 0.333333
2314 qwertyu0997 | 0.333333
2315 qwertyu0998 | 0.333333
2316 qwertyu0999 | 0.333333
2319 select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
2321 -------------+----------
2322 qwertyu0988 | 0.333333
2326 select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
2328 ---------------------------------------------------
2330 -> Index Scan using trgm_idx on test_trgm
2331 Order By: (t <-> 'q0987wertyu0988'::text)
2334 select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;
2336 ----------+-------------
2337 0.411765 | qwertyu0988
2341 drop index trgm_idx;
2342 create index trgm_idx on test_trgm using gin (t gin_trgm_ops);
2343 set enable_seqscan=off;
2344 select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
2346 -------------+----------
2348 qwertyu0980 | 0.714286
2349 qwertyu0981 | 0.714286
2350 qwertyu0982 | 0.714286
2351 qwertyu0983 | 0.714286
2352 qwertyu0984 | 0.714286
2353 qwertyu0985 | 0.714286
2354 qwertyu0986 | 0.714286
2355 qwertyu0987 | 0.714286
2356 qwertyu0989 | 0.714286
3346 qwertyu1000 | 0.411765
3349 select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
3351 -------------+----------
3353 qwertyu0980 | 0.411765
3354 qwertyu0981 | 0.411765
3355 qwertyu0982 | 0.411765
3356 qwertyu0983 | 0.411765
3357 qwertyu0984 | 0.411765
3358 qwertyu0985 | 0.411765
3359 qwertyu0986 | 0.411765
3360 qwertyu0987 | 0.411765
3361 qwertyu0989 | 0.411765
3362 qwertyu0088 | 0.333333
3363 qwertyu0098 | 0.333333
3364 qwertyu0188 | 0.333333
3365 qwertyu0288 | 0.333333
3366 qwertyu0388 | 0.333333
3367 qwertyu0488 | 0.333333
3368 qwertyu0588 | 0.333333
3369 qwertyu0688 | 0.333333
3370 qwertyu0788 | 0.333333
3371 qwertyu0888 | 0.333333
3372 qwertyu0900 | 0.333333
3373 qwertyu0901 | 0.333333
3374 qwertyu0902 | 0.333333
3375 qwertyu0903 | 0.333333
3376 qwertyu0904 | 0.333333
3377 qwertyu0905 | 0.333333
3378 qwertyu0906 | 0.333333
3379 qwertyu0907 | 0.333333
3380 qwertyu0908 | 0.333333
3381 qwertyu0909 | 0.333333
3382 qwertyu0910 | 0.333333
3383 qwertyu0911 | 0.333333
3384 qwertyu0912 | 0.333333
3385 qwertyu0913 | 0.333333
3386 qwertyu0914 | 0.333333
3387 qwertyu0915 | 0.333333
3388 qwertyu0916 | 0.333333
3389 qwertyu0917 | 0.333333
3390 qwertyu0918 | 0.333333
3391 qwertyu0919 | 0.333333
3392 qwertyu0920 | 0.333333
3393 qwertyu0921 | 0.333333
3394 qwertyu0922 | 0.333333
3395 qwertyu0923 | 0.333333
3396 qwertyu0924 | 0.333333
3397 qwertyu0925 | 0.333333
3398 qwertyu0926 | 0.333333
3399 qwertyu0927 | 0.333333
3400 qwertyu0928 | 0.333333
3401 qwertyu0929 | 0.333333
3402 qwertyu0930 | 0.333333
3403 qwertyu0931 | 0.333333
3404 qwertyu0932 | 0.333333
3405 qwertyu0933 | 0.333333
3406 qwertyu0934 | 0.333333
3407 qwertyu0935 | 0.333333
3408 qwertyu0936 | 0.333333
3409 qwertyu0937 | 0.333333
3410 qwertyu0938 | 0.333333
3411 qwertyu0939 | 0.333333
3412 qwertyu0940 | 0.333333
3413 qwertyu0941 | 0.333333
3414 qwertyu0942 | 0.333333
3415 qwertyu0943 | 0.333333
3416 qwertyu0944 | 0.333333
3417 qwertyu0945 | 0.333333
3418 qwertyu0946 | 0.333333
3419 qwertyu0947 | 0.333333
3420 qwertyu0948 | 0.333333
3421 qwertyu0949 | 0.333333
3422 qwertyu0950 | 0.333333
3423 qwertyu0951 | 0.333333
3424 qwertyu0952 | 0.333333
3425 qwertyu0953 | 0.333333
3426 qwertyu0954 | 0.333333
3427 qwertyu0955 | 0.333333
3428 qwertyu0956 | 0.333333
3429 qwertyu0957 | 0.333333
3430 qwertyu0958 | 0.333333
3431 qwertyu0959 | 0.333333
3432 qwertyu0960 | 0.333333
3433 qwertyu0961 | 0.333333
3434 qwertyu0962 | 0.333333
3435 qwertyu0963 | 0.333333
3436 qwertyu0964 | 0.333333
3437 qwertyu0965 | 0.333333
3438 qwertyu0966 | 0.333333
3439 qwertyu0967 | 0.333333
3440 qwertyu0968 | 0.333333
3441 qwertyu0969 | 0.333333
3442 qwertyu0970 | 0.333333
3443 qwertyu0971 | 0.333333
3444 qwertyu0972 | 0.333333
3445 qwertyu0973 | 0.333333
3446 qwertyu0974 | 0.333333
3447 qwertyu0975 | 0.333333
3448 qwertyu0976 | 0.333333
3449 qwertyu0977 | 0.333333
3450 qwertyu0978 | 0.333333
3451 qwertyu0979 | 0.333333
3452 qwertyu0990 | 0.333333
3453 qwertyu0991 | 0.333333
3454 qwertyu0992 | 0.333333
3455 qwertyu0993 | 0.333333
3456 qwertyu0994 | 0.333333
3457 qwertyu0995 | 0.333333
3458 qwertyu0996 | 0.333333
3459 qwertyu0997 | 0.333333
3460 qwertyu0998 | 0.333333
3461 qwertyu0999 | 0.333333
3464 select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
3466 -------------+----------
3467 qwertyu0988 | 0.333333
3470 create table test2(t text COLLATE "C");
3471 insert into test2 values ('abcdef');
3472 insert into test2 values ('quark');
3473 insert into test2 values (' z foo bar');
3474 create index test2_idx_gin on test2 using gin (t gin_trgm_ops);
3475 set enable_seqscan=off;
3477 select * from test2 where t like '%BCD%';
3479 ------------------------------------------
3480 Bitmap Heap Scan on test2
3481 Recheck Cond: (t ~~ '%BCD%'::text)
3482 -> Bitmap Index Scan on test2_idx_gin
3483 Index Cond: (t ~~ '%BCD%'::text)
3487 select * from test2 where t ilike '%BCD%';
3489 -------------------------------------------
3490 Bitmap Heap Scan on test2
3491 Recheck Cond: (t ~~* '%BCD%'::text)
3492 -> Bitmap Index Scan on test2_idx_gin
3493 Index Cond: (t ~~* '%BCD%'::text)
3496 select * from test2 where t like '%BCD%';
3501 select * from test2 where t like '%bcd%';
3507 select * from test2 where t like E'%\\bcd%';
3513 select * from test2 where t ilike '%BCD%';
3519 select * from test2 where t ilike 'qua%';
3525 select * from test2 where t like '%z foo bar%';
3531 select * from test2 where t like ' z foo%';
3538 select * from test2 where t ~ '[abc]{3}';
3540 --------------------------------------------
3541 Bitmap Heap Scan on test2
3542 Recheck Cond: (t ~ '[abc]{3}'::text)
3543 -> Bitmap Index Scan on test2_idx_gin
3544 Index Cond: (t ~ '[abc]{3}'::text)
3548 select * from test2 where t ~* 'DEF';
3550 ------------------------------------------
3551 Bitmap Heap Scan on test2
3552 Recheck Cond: (t ~* 'DEF'::text)
3553 -> Bitmap Index Scan on test2_idx_gin
3554 Index Cond: (t ~* 'DEF'::text)
3557 select * from test2 where t ~ '[abc]{3}';
3563 select * from test2 where t ~ 'a[bc]+d';
3569 select * from test2 where t ~ '(abc)*$';
3577 select * from test2 where t ~* 'DEF';
3583 select * from test2 where t ~ 'dEf';
3588 select * from test2 where t ~* '^q';
3594 select * from test2 where t ~* '[abc]{3}[def]{3}';
3600 select * from test2 where t ~* 'ab[a-z]{3}';
3606 select * from test2 where t ~* '(^| )qua';
3612 select * from test2 where t ~ 'q.*rk$';
3618 select * from test2 where t ~ 'q';
3624 select * from test2 where t ~ '[a-z]{3}';
3632 select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
3637 select * from test2 where t ~ 'z foo bar';
3643 select * from test2 where t ~ ' z foo bar';
3649 select * from test2 where t ~ ' z foo bar';
3655 select * from test2 where t ~ ' z foo';
3661 drop index test2_idx_gin;
3662 create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
3663 set enable_seqscan=off;
3665 select * from test2 where t like '%BCD%';
3667 ------------------------------------------
3668 Index Scan using test2_idx_gist on test2
3669 Index Cond: (t ~~ '%BCD%'::text)
3673 select * from test2 where t ilike '%BCD%';
3675 ------------------------------------------
3676 Index Scan using test2_idx_gist on test2
3677 Index Cond: (t ~~* '%BCD%'::text)
3680 select * from test2 where t like '%BCD%';
3685 select * from test2 where t like '%bcd%';
3691 select * from test2 where t like E'%\\bcd%';
3697 select * from test2 where t ilike '%BCD%';
3703 select * from test2 where t ilike 'qua%';
3709 select * from test2 where t like '%z foo bar%';
3715 select * from test2 where t like ' z foo%';
3722 select * from test2 where t ~ '[abc]{3}';
3724 ------------------------------------------
3725 Index Scan using test2_idx_gist on test2
3726 Index Cond: (t ~ '[abc]{3}'::text)
3730 select * from test2 where t ~* 'DEF';
3732 ------------------------------------------
3733 Index Scan using test2_idx_gist on test2
3734 Index Cond: (t ~* 'DEF'::text)
3737 select * from test2 where t ~ '[abc]{3}';
3743 select * from test2 where t ~ 'a[bc]+d';
3749 select * from test2 where t ~ '(abc)*$';
3757 select * from test2 where t ~* 'DEF';
3763 select * from test2 where t ~ 'dEf';
3768 select * from test2 where t ~* '^q';
3774 select * from test2 where t ~* '[abc]{3}[def]{3}';
3780 select * from test2 where t ~* 'ab[a-z]{3}';
3786 select * from test2 where t ~* '(^| )qua';
3792 select * from test2 where t ~ 'q.*rk$';
3798 select * from test2 where t ~ 'q';
3804 select * from test2 where t ~ '[a-z]{3}';
3812 select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
3817 select * from test2 where t ~ 'z foo bar';
3823 select * from test2 where t ~ ' z foo bar';
3829 select * from test2 where t ~ ' z foo bar';
3835 select * from test2 where t ~ ' z foo';