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?


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 ? – Rafael Nobre Oct 22 '18 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. – Daniel Vérité Oct 23 '18 at 12:10

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.