How to select bytea data as binary data, inserted as hex-encoded from binary 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.
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.