Implement PostgreSQL full text search

From: - <grandebuzon(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Implement PostgreSQL full text search
Date: 2011-01-12 09:14:17
Message-ID: AANLkTindQHpXNUp1558PDuD42g_JHY0QRTz5vDp2AztJ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Browse pgsql-sql by date

  From Date Subject
Next Message Protasov Vladimir 2011-01-12 12:41:13 INSERT/UPDATE .. RETURNING
Previous Message Amar Dhole 2011-01-12 08:18:50 Re: help needs in converting db2 function in postgresql.