트라이그램 검색(pg_trgm)으로 퍼지 매칭하기

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT show_trgm('database');
-- {dat, aba, atab, tab, abas, base, ase, ...}

SELECT similarity('database', 'datbase');     -- ~0.75
SELECT similarity('hello', 'world');             -- ~0.0

SELECT 'database' % 'datbase';      -- t
SET pg_trgm.similarity_threshold = 0.4;

CREATE INDEX users_name_trgm ON users USING GIN (full_name gin_trgm_ops);

-- Now this is index-supported:
SELECT * FROM users WHERE full_name ILIKE '%ali%';

SELECT full_name, similarity(full_name, 'datbase') AS sim
FROM users
WHERE full_name % 'datbase'
ORDER BY sim DESC
LIMIT 10;

SELECT full_name
FROM users
WHERE full_name % 'alice'
ORDER BY full_name <-> 'alice'
LIMIT 10;

SELECT name
FROM products
WHERE name % $1
   OR name ILIKE $1 || '%'
ORDER BY similarity(name, $1) DESC
LIMIT 8;

EXPLAIN ANALYZE
SELECT * FROM users
WHERE full_name ILIKE '%ali%' AND country = 'US';