PostgreSQL: ILIKE in all text columns

Question

Is there a simple ad-hoc way to execute a ILIKE query on all text columns of a table?

I know that there is a cell which contains "foobar". But the table has a lot of columns and only 50k rows, so searching all columns and rows should be fast.

score 10 · Accepted Answer · answered May 18 '21 at 16:13

10

I'm giving you this query with the understanding that this is NOT something you'd use for performance, just backfill and cleanup (which seems to be the case here):

SELECT * 
FROM tablename foo 
WHERE LOWER(foo::text) LIKE '%foobar%'

If you want to implement some moderately performant free text searching, you'd want to go through the effort of creating an indexed tsvector/weighted tsvector column.

answered May 18 '21 at 16:13

John Morton

434
3
8

Wow, that's cool. Great! Just cast the whole thing to "text". Thank you! – guettli May 18 '21 at 16:44
BTW, why do you create the alias "foo"? works fine without it. – guettli May 18 '21 at 16:44
1

It's not really necessary, but it is a good habit to get into. Inevitably, ad hoc queries will evolve into massive behemoths with joins and correlated subqueries, but here, it saved me a few characters of typing. – John Morton May 18 '21 at 17:00

Erwin Brandstetter · Answer 2 · 2021-06-22T15:10:59.673

To be unambiguous, you can write:

SELECT * 
FROM   tablename foo
WHERE  foo.*::text ILIKE '%foobar%';

A nested column of the same name (foo in this case) would take precedence. The more verbose syntax foo.* makes it an unambiguous reference to the table row.

About LIKE vs. ILIKE (and index support):

LOWER LIKE vs iLIKE

Closely related:

For a more targeted search (fails the "ad-hoc" requirement), to only include string-type columns in the search, and exclude corner-case false positives that the above can produce, you could use dynamic SQL along these lines:

Replace empty strings with null values

PostgreSQL: ILIKE in all text columns

2 Answers2