CODEWITHSUNDEEP
sqlpostgresqlsearchindexingfull-text-search
smartmouse
smartmouse

Reputation: 14404

Postgres full text search: how to search multiple words in multiple fields?

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?

Upvotes: 9

Views: 16692

Answers (1)

Denys Séguret
Denys Séguret

Reputation: 382454

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.

Upvotes: 13

Related Questions