I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of SELECTing the whole byte string into the memory?

1 Answer 1


Of course, just use the substring function:

select substring(bytea_contents from offset for length) FROM table;

or get_byte(bytea_contents,offset) to get a single byte as an integer.

See Binary String Functions and Operators in the doc for all functions.

  • Is this efficient? Or does it actually read the whole column and discards unwanted output ? Oct 22, 2018 at 17:57
  • It depends. If the contents got compressed, it will have to read the whole column. See heap_tuple_untoast_attr_slice in the source code for the implementation. Oct 23, 2018 at 12:10

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.