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%'
  • 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 – a_horse_with_no_name Oct 16 '18 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 Oct 16 '18 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 – a_horse_with_no_name Oct 16 '18 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 Oct 16 '18 at 13:35
  • Actually it won't blow up, it just won't produce meaningful results. – jjanes Oct 16 '18 at 13:39
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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