8

i'm using for the first time Postgresql and i'm trying to create a search engine in my website. i have this table:

CREATE TABLE shop (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  address TEXT NOT NULL,
  city TEXT NOT NULL
);

Then i created an index for every field of the table (is this the right way? Or maybe i can create one index for all fields?):

CREATE INDEX shop_name_fts ON shop USING gin(to_tsvector('italian', name));
CREATE INDEX shop_desc_fts ON shop USING gin(to_tsvector('italian', description));
CREATE INDEX shop_addr_fts ON shop USING gin(to_tsvector('italian', address));
CREATE INDEX shop_city_fts ON shop USING gin(to_tsvector('italian', city));

Now, what is the SQL query if i want to search one word in every index?

I tried this and it works:

SELECT id FROM shop WHERE to_tsvector(name) @@ to_tsquery('$word') OR
                          to_tsvector(description) @@ to_tsquery('$word') OR 
                          to_tsvector(address) @@ to_tsquery('$word') OR 
                          to_tsvector(city) @@ to_tsquery('$word')

Does exist a better way to do the same? Can i search to_tsquery into multiple to_tsvector? A friend of mine suggests a solution, but it is for MySQL database:

SELECT * FROM shop WHERE MATCH(name, description, address, city) AGAINST('$word')

What is the solution for Postgresql?

In addition, can i search multiple to_tsquery into multiple to_tsvector? what is the SQL query if i want to search two words or more than one word? Can i just pass "two words" to $word from PHP? If i can, how does it work? Does it search for first word AND second one or first word OR second one?

smartmouse
  • 13,912
  • 34
  • 100
  • 166
  • Why do you even want to do a full text search on a name or a city ? You should probably do much simpler comparisons on those fields. – Denys Séguret Jun 05 '15 at 09:16
  • These are sample data. My database contains shops data, that the name could me "The great restaurant". I'm going to edit my question, sorry. – smartmouse Jun 05 '15 at 09:19

1 Answers1

13

It looks like what you want is, in fact to search the concatenation of all those fields.

You could build a query doing exactly this

... where to_tsvector('italian', name||' '||coalesce(decription,'')...) @@ to_tsquery('$word')

and build an index on the exact same computation:

create index your_index on shop
using GIN(to_tsvector('italian',name||' '||coalesce(decription,'')...))

Don't forget to use coalesce on columns accepting NULL values.

Denys Séguret
  • 372,613
  • 87
  • 782
  • 758
  • It sounds good, but i get this erro: `ERROR: functions in index expression must be marked IMMUTABLE` – smartmouse Jun 05 '15 at 09:45
  • I solved with this: `create index shop_fts on shop using GIN(to_tsvector('italian', name || ' ' || description || ' ' || address || ' ' || city));` – smartmouse Jun 05 '15 at 09:52
  • So now, how to search for multiple words? And how does it work? – smartmouse Jun 05 '15 at 09:59
  • Use the exact same phrasing in the index and in the where part. – Denys Séguret Jun 05 '15 at 10:00
  • I add `coalesce` to my previous `create index` query. Thank you for the hint. – smartmouse Jun 05 '15 at 10:10
  • Now, if you tell me how to search for multiple words and how to use AND and OR with them... i can click to accept your answer :) – smartmouse Jun 05 '15 at 10:12
  • 4
    For multiple words search, use the syntax of `to_tsquery` with operators `&`, `|` and `!`: `to_tsquery('molto & facile' )`. More details here : http://www.postgresql.org/docs/9.2/static/textsearch-intro.html – Denys Séguret Jun 05 '15 at 10:20
  • Can you tell me how to search for a word except another word? I'm trying to write `word1 !word2` but it gives me error. It works only if i use `word1&!word2`... is that right? – smartmouse Jun 10 '15 at 14:48