0

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%'
Improve this question
8
  • 2
    Why do you compare the byte array with another column of your table? Or did you mean to write LIKE 'FOO' - which would be identical to = 'FOO'. But in general create index on the_table ( (UPPER("my_bytea_col"::text) text_pattern_ops) would be usable by LIKE. But you can't index column values that are bigger than approximately 2K - if your bytea column is bigger than that, you won't be able to insert rows containing data exceeding that limit
    – user1822
    Commented Oct 16, 2018 at 7:32
  • @a_horse_with_no_name I am sorry. This was a typo. I updated the question. Yes, the column values will be bigger than 2K. Does the 2K limit apply to bytea only?
    – guettli
    Commented Oct 16, 2018 at 8:30
  • 1
    No, the 2k limit is a general restriction on the size of an index entry. See here and here and here
    – user1822
    Commented Oct 16, 2018 at 8:41
  • If the column can contain binary data, then the "my_bytea_col"::text inside the UPPER call is going to blow up. So you have to decide, is it binary or is it not?
    – jjanes
    Commented Oct 16, 2018 at 13:35
  • Actually it won't blow up, it just won't produce meaningful results.
    – jjanes
    Commented Oct 16, 2018 at 13:39

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.