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 only takes a minute to sign up.
Sign up to join this community
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.
