CODEWITHSUNDEEP
sqlpostgresql
Syed Asad Abbas Zaidi
Syed Asad Abbas Zaidi

Reputation: 1066

Postgresql: Match complete word contained in an array

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.

Upvotes: 1

Views: 61

Answers (3)

Vao Tsun
Vao Tsun

Reputation: 51416

In this case changing

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

to

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

should be enough?..

Upvotes: 0

Sergey Gornostaev
Sergey Gornostaev

Reputation: 7777

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);

Upvotes: 1

JRA
JRA

Reputation: 487

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[])

Upvotes: 0

Related Questions