Sonetto
959d813630
* feat(db): add pg_trgm search index on posts * feat(api): use pg_trgm similarity search for posts * fix(api): use ILIKE + word_similarity instead of % operator for search
12 lines
353 B
SQL
12 lines
353 B
SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
ALTER TABLE posts ADD COLUMN IF NOT EXISTS search_text TEXT
|
|
GENERATED ALWAYS AS (
|
|
COALESCE(title, '') || ' ' ||
|
|
COALESCE(summary, '') || ' ' ||
|
|
COALESCE(content_md, '')
|
|
) STORED;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_search_trgm
|
|
ON posts USING GIN (search_text gin_trgm_ops);
|