I use the unusual bytea column type in a PostgreSQL DB because the data in this column is like a blob. I mostly contains text, but sometimes it contains binary data.
Which index would fit if the query like this gets done?
WHERE (UPPER("my_bytea_col"::text) LIKE '%FOO%'
You could create an immutable function which changes the \000 null characters (plus any other characters you consider to be binary) to spaces, then create a trigram index on that function over the column. There might be such a function built in, but if so I don't know of it.
