Extracting binary data from postgreSQL

Recently, we have migrated our database from Oracle to PostgreSQL. We are storing the messages in form of blobs in our tables. As part of the migration, we have to validate that the blobs we store are retrievable. Googling around, it turns out it is not that straightforward to extract the blob in postgreSQL, at least not as staightforward as in Oracle.

From here, you could use encode and copy. But problem is that it copies it to an hex encoded file that I have to decode somewhere. So instead of copying it to a file, I just encode it and copy the result to any online site that converts hex encoded files to binary files such as tomeko

So here are the steps

  1. SELECT encode(msg_content, ‘hex’) FROM schema.audit_message_content LIMIT 1;
  2. Copy result to tomeko and download the file.