The post How to insert (file) data into a PostgreSQL bytea column was helpful.

However, when I select these columns, I get the same hex encoded data.

For instance:

> select substring(smallblob,0,64) from longdata where blobi d=1;
\xd0cf11e0a1b11ae1000000000000000000000000000000003b000300feff09000600000000000 00000000000010000006a0000000000000000100000670000 
(1 row)

How can I SELECT this data in binary format? CAST' andCONVERT` do not appear to support a 'binary' option.

  • why do you want binary data on your screen? it will include all sorts of nasty surprises if you do... Commented Apr 11, 2012 at 7:28

1 Answer 1


You're not going to get binary data out of psql and SELECT. psql will format it for readability. You could try something with lo_export similar to the answer in How to insert (file) data into a PostgreSQL bytea column to get the data written to a file.

However, most PostgreSQL client adapters (perl DBI, Ruby 'pg', etc) can give you the data in binary when you SELECT bytea columns. Just write a little program using an appropriate adapter in the language of your choice.

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.