Postgresql: Match complete word contained in an array

Question

I have a query as:

select id 
from events 
where description is not null 
  and (name ilike any (query_arr) 
       or description ilike any (query_arr) 
       or additional_info ilike any (query_arr) 
       or venue_name ilike any (query_arr) 
       or other_category ilike any (query_arr) 
       or eventful_category ilike any (query_arr) )

And query_arr is:

{'%Tomato%','%Potato%','%Pines%'}

But now I need to match the complete word instead of ilike % sign, since it fails for a case where if the description is 'Porcupines are rodentian mammals' then the query_arr word 'pines' gets matched which is incorrect.

So I need to match the complete word itself any where in the table columns being queried.

you can just add spaces around searchwords, like ` select 'Porcupines are rodentian mammals' ~* ' pines ';` instead of ` select 'Porcupines are rodentian mammals' ~* 'pines';` — Vao Tsun
– Vao Tsun, Commented Nov 30, 2016 at 8:33
field ~* any(array['\mTomato\M','\mPotato\M','\mPines\M']). But FTS looks promising. — Abelisto
– Abelisto, Commented Nov 30, 2016 at 9:24

Sergey Gornostaev · Accepted Answer · 2016-11-30 06:08:53Z

1

FTS - is a large and complex subject. Don't use my example as is.

select id from events where to_tsquery('Tomato Potato Pines') @@ to_tsvector(events::text);

answered Nov 30, 2016 at 6:08

Sergey Gornostaev

7,8373 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JRA · Accepted Answer · 2016-11-30 06:04:47Z

0

Probably you can use this syntax in PostgreSql

~*ANY('{query_arr}'::text[])

Your Query Should be like this

select id from events where description is not null and (name ~*ANY('{query_arr}'::text[]) or description ~*ANY('{query_arr}'::text[]) or additional_info ~*ANY('{query_arr}'::text[]) or venue_name ~*ANY('{query_arr}'::text[]) or other_category ~*ANY('{query_arr}'::text[]) or eventful_category ~*ANY('{query_arr}'::text[])

answered Nov 30, 2016 at 6:04

JRA

4876 silver badges19 bronze badges

Comments

Vao Tsun · Accepted Answer · 2016-11-30 08:34:38Z

0

In this case changing

{'%Tomato%','%Potato%','%Pines%'}

to

{'% Tomato %','% Potato %','% Pines %'}

should be enough?..

answered Nov 30, 2016 at 8:34

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgresql: Match complete word contained in an array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related