Hello everyone.
I'm trying to create search with pagination and filters on a website. I
stumbled with the search for the words. It could do with ILIKE but the table
will have many records, was looking at PostgreSQL full text search, but do
not quite understand how to implement. Someone could help me with
implementation?

my select is

SELECT entries. "id" entries. "title" entries. "description" entries.
"price"
FROM
(SELECT ROW_NUMBER () OVER (
ORDER BY items. "date" DESC
) as Row,
items. "id" items. "title" items. "description" items. "price "
FROM items, provinces
WHERE
items. "prov_id" = provinces. "prov_id"
AND items. "prov_id"= 2

- I try to change here for PostgreSQL full text search
AND (items. "description" ILIKE ('% TEST search Postgre%') OR
items. "title" ILIKE ('% search Postgre% TEST'))
) as entries

I want to look for words entered in the title and description columns of the
table.
Thank you very much for any help.



En Español

Hola a todos.
Estoy intentando crear busqueda con paginacion y filtros en una pagina web.
Me tropeze con la busqueda por las palabras. Se podria hacer con ILIKE pero
la tabla va a tener muchos registros, estaba mirando a postgreSQL full text
search, pero no entiendo bien como implementar. Me podria ayudar alquien con
implementacion?

mi select es

SELECT entries."id", entries."title", entries."description", entries."price"
FROM
(SELECT ROW_NUMBER() OVER (
ORDER BY items."date" DESC
) as Row,
items."id", items."title", items."description", items."price"
FROM items, provinces
WHERE
items."prov_id"=provinces."prov_id"
AND items."prov_id"=2

-- I try to change here for postgreSQL full text search
AND (items."description" ILIKE('%TEST search postgre%') OR
items."title" ILIKE('%TEST search postgre%'))
) as entries

Quiero que busque palabras introducidas en las columnas title y description
de la tabla.
Muchas gracias por cualquier ayuda.