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.
PostgreSQL: ILIKE in all text columns
Viewed 939
2 Answers
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.
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):
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:
Related