Simple full text search in PostgreSQL
Start a more relevant text search than LIKE using tsvector and tsquery.
You are reading a translated version.
Limitations of LIKE for search
LIKE '%kata%' works for simple matching, but doesn't understand wordforms, can't sort by relevance, and is difficult to optimize with indexes on long texts.
Setting up the search field
ALTER TABLE artikel ADD COLUMN pencarian tsvector
GENERATED ALWAYS AS (to_tsvector('indonesian', judul || ' ' || isi)) STORED;
CREATE INDEX idx_artikel_pencarian ON artikel USING GIN (pencarian);
Column pencarian automatically fills in from the combined title and body each time the line changes, as it uses GENERATED ALWAYS AS ... STORED.
Running a search
SELECT judul
FROM artikel
WHERE pencarian @@ to_tsquery('indonesian', 'database & indeks')
ORDER BY ts_rank(pencarian, to_tsquery('indonesian', 'database & indeks')) DESC;
to_tsquery accept operators such as & AND | (or), and ! (not) to structure search conditions. ts_rank give a relevance score that can be used to sort the results.
Language configuration affects results
Config indonesian determine how the root word is recognized. Additional words such as "search" and "searching" can be considered similar depending on the dictionary used in the configuration.
When a more complete solution is needed
For more complex search needs, such as typo tolerance or large-scale cross-language searches, specialized search engines such as Elasticsearch or Meilisearch are usually more suitable than the database's built-in full text search.
practice;
Add different weights between the title and the content of the article using setweightthen watch how the order of the search results changes.