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 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? 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 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? Commented Oct 16, 2018 at 13:35
  • Actually it won't blow up, it just won't produce meaningful results. 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.