Wymored Login

Como usar Full Text Search - FTS no Postgresql

07 de janeiro de 2019 por Alexandre Miguel de Andrade Souza

Aqui estão os passos básicos:

1) Opcional: crie um banco de dados e algumas tabelas (substitua ` por aspas simples no código) :

CREATE DATABASE blog;
CREATE USER blog WITH PASSWORD `blog`;
GRANT ALL ON DATABASE blog TO blog;

create extension unaccent;

DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    hashed_password VARCHAR(100) NOT NULL
);

DROP TABLE IF EXISTS entries;
CREATE TABLE entries (
    id SERIAL PRIMARY KEY,
    author_id INT NOT NULL REFERENCES authors(id),
    slug VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(512) NOT NULL,
    markdown TEXT NOT NULL,
    html TEXT NOT NULL,
    search_tsv tsvector;
    published TIMESTAMP NOT NULL,
    updated TIMESTAMP NOT NULL
);

CREATE INDEX ON entries (published);

O detalhe é o campo search_tsv do tipo tsvector, na tabela entries

2) Após inserir ou atualizar dados na tabela, execute uma consulta para atualizar o campo tsvector:

update entries e set search_tsv = tsv.document
    from (select e.id, to_tsvector(`portuguese`, 
    unaccent(a.name || ` ` ||
    e.title || ` ` ||
    e.markdown
    )) as document
    from entries e,  authors a 
    where e.author_id = a.id
    ) as tsv
where e.id = tsv.id
and e.id=1

ou retire a última linha se já tiver muitos dados e atualizar toda a tabela.

3) O último passo é fazer a busca:

    SELECT e.*, ts_rank(search_tsv, q) as rank
    FROM entries e, PLAINTO_TSQUERY(`busca`) as q
    where ts_rank(search_tsv, q) > 0.01
    order by rank desc

o where ts_rank(search_tsv, q) > 0.01 é importante para filtrar a relevância, porque se não ele retorna todos os registros. o percentual pode ser alterado, de acordo com a relevância mínima que desejar. Faça alguns testes.