PGSQL Toate comenzile și funcțiile PostgreSQL

`Căutare Full-Text Avansată în PostgreSQL (to_tsvector, to_tsquery și GIN)` în PostgreSQL

Interogări și filtrare specifică Postgres PostgreSQL 10+

Ghid complet pentru motorul nativ de Full-Text Search în PostgreSQL: parsare lexicală, stemming pe limba română, căutare booleană și de proximitate (<->), relevanță cu ts_rank și indecși GIN.

Sintaxă SQL

SELECT * FROM articole WHERE to_tsvector('romanian', titlu || ' ' || text) @@ to_tsquery('romanian', 'baze & date');

Pentru multe aplicații, adăugarea unui cluster extern de ElasticSearch sau Meilisearch aduce costuri uriașe de infrastructură, sincronizare și mentenanță. PostgreSQL include un motor nativ complet de căutare Full-Text (FTS) capabil să gestioneze milioane de documente cu suport pentru dicționare lingvistice, eliminare de stop-words, reducere la rădăcină (stemming), calcul de relevanță și căutare de fraze.

Cum funcționează căutarea Full-Text în PostgreSQL

-- 1. to_tsvector convertește un text brut într-o listă sortată de lexeme unice cu pozițiile lor:
SELECT to_tsvector('romanian', 'Optimizarea bazelor de date și indecșii în PostgreSQL');
-- Rezultat: 'baz':2 'dat':4 'indecs':6 'optimizar':1 'postgresql':8

-- 2. to_tsquery creează o expresie logică de căutare:
SELECT to_tsquery('romanian', 'optimizare & indecsi');
-- Rezultat: 'optimizar' & 'indecs'

-- 3. Operatorul @@ verifică potrivirea dintre vector și interogare:
SELECT to_tsvector('romanian', 'Ghid de optimizare a indecsilor') @@ to_tsquery('romanian', 'optimizare & indecs');
-- Returnează TRUE! (stemming-ul a recunoscut automat că 'indecsilor' are aceeași rădăcină cu 'indecs')

Operatori Logici în to_tsquery

Operator	Semnificație	Exemplu de Sintaxă
`&`	AND: Ambele lexeme trebuie să fie prezente în document.	`to_tsquery('romanian', 'sql & postgresql')`
`\|`	OR: Cel puțin unul dintre termeni trebuie să fie prezent.	`to_tsquery('romanian', 'mysql \| postgresql')`
`!`	NOT: Termenul nu trebuie să apară în document.	`to_tsquery('romanian', 'sql & !oracle')`
`<->`	Căutare de proximitate (Phrase search): Termenul 2 trebuie să urmeze imediat după Termenul 1.	`to_tsquery('romanian', 'baze <-> date')`

Producție: Coloană Calculată Stocată (STORED) și Index GIN

-- Adăugăm o coloană calculată automată generată de PostgreSQL la fiecare INSERT/UPDATE:
CREATE TABLE articole (
    id SERIAL PRIMARY KEY,
    titlu VARCHAR(255) NOT NULL,
    continut TEXT NOT NULL,
    -- tsvector generat automat și stocat pe disc:
    tsv_cautare TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('romanian', coalesce(titlu, '')), 'A') ||
        setweight(to_tsvector('romanian', coalesce(continut, '')), 'B')
    ) STORED
);

-- Creăm indexul GIN care oferă răspunsuri în sub 5 milisecunde:
CREATE INDEX idx_articole_tsv ON articole USING gin (tsv_cautare);

-- Interogare cu ordonare după relevanță (ts_rank):
SELECT id, titlu, ts_rank(tsv_cautare, to_tsquery('romanian', 'tranzactii & acid')) AS relevanta
FROM articole
WHERE tsv_cautare @@ to_tsquery('romanian', 'tranzactii & acid')
ORDER BY relevanta DESC
LIMIT 10;

Observați utilizarea funcției setweight(): ea acordă o greutate superioară (ponderația ‘A’) cuvintelor din titlu comparativ cu cele din corpul articolului (ponderația ‘B’), asigurând o ierarhizare perfectă a rezultatelor pentru utilizatori.