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.

StackExchange Database Administrators Q#13276, answer score: 3