PostgreSQL: ILIKE in all text columns
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 fooWHERE 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: