3

Assuming I have a table with a bytea column called data on a table called t, how can I:

  1. Write a query that returns all rows which have a specific prefix on their data column.
  2. Index the query.

I am using Postgresql 9.5.

1 Answer 1

5

For searching by prefix, you just do it. The hard part (at least from the command line) is getting the binary data to be queried into query in a properly escaped way.

To query a table of file-blobs for files with the ELF header:

select....where data like (E'\\x7f')::bytea||'ELF%'

For indexing it, the main problem you will have is that PostgreSQL btree indexes can't index values longer than 1/3 of the page size (2712 bytes, usually) and a column of binary data is likely to have some records that exceed that. You can build a functional index on only the first so many bytes of the data, and query that.

create index on thing (substring(data,1,100));

And you can then query it like this:

 select....where substring(data,1,100) like (E'\\x7f')::bytea||'ELF%';

PostgreSQL is not smart enough to realize that for a prefix to match the full column, it most also match a longer-than-the-query prefix of the column. So that means you must write the match against the expression used to build the index, not against the column itself, or the index won't be used.

4
  • Is there no escaping issue? What if my byte string pattern contains something that has a meaning for the LIKE operator?
    – Flavien
    Mar 3, 2016 at 11:04
  • Yes, there is an escaping issue. If the prefix has \ or % or _ in you would have to backwhack them. I would do that in the client, as doing it in SQL would be a pain.
    – jjanes
    Mar 8, 2016 at 15:46
  • The incices for substring start at 1. In You application you only inex over the first 99 characters of data
    – thejonny
    Jan 30, 2023 at 17:50
  • @thejonny Sure enough, fixed.
    – jjanes
    Jan 30, 2023 at 18:47

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.