Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
why do you want binary data on your screen? it will include all sorts of nasty surprises if you do... –  Jack Douglas Apr 11 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.