Query for iLike list in PostgreSQL

Question

I'm writing an inefficient query as follows:

WHERE (Field iLIKE '%cat%' OR Field iLIKE '%dog%' OR Field  iLIKE '%animal%' OR Field  iLIKE '%pet%') 

whereas what I would like to write is:

WHERE Field iLIKE ('%cat%','%dog%','%animal%','%pet%')

Is there an easy way to accomplish this?

Answer 1

You can get the powerful regular expression operators ~ and ~* for case insensitive:

WHERE Field ~* '(cat|dog|animal|pet)'

SIMILAR TO is case sensitive, so you'd have to do:

WHERE Field SIMILAR TO '%([Cc][Aa][Tt]|[Dd][Oo][Gg]|[Aa][Nn][Ii][Mm][Aa][Ll]|[Pp][Ee][Tt])%'

ANY ARRAY will work too, but the perfomance is worse:

WHERE Field ILIKE ANY (array['%cat%','%dog%','%animal%','%pet%'])

With some dummy data with 1000000 rows with a BTREE INDEX in Field, I get these results:

╔═════════════╦═════════════╗
║  Operator   ║ Time (secs) ║
╠═════════════╬═════════════╣
║ ~*          ║ 1.5         ║
║ SIMILAR TO  ║ 1.4         ║
║ ANY ARRAY   ║ 4.0         ║
║ OR OR OR... ║ 4.0         ║
╚═════════════╩═════════════╝

Answer 2

select 'cata' ilike any (array['%cat%','%dog%','%animal%','%pet%']);
 ?column? 
----------
 t