How to download Postgres bytea column as file

Question

Currently, i have a number of files stored in postgres 8.4 as bytea. The file types are .doc, .odt, .pdf, .txt and etc.

May i know how to download all the file stored in Postgres because i need to to do a backup. I need them in their original file type instead of bytea format.

Thanks!

Solution 1

One simple option is to use COPY command with encode to hex format and then apply xxd shell command (with -p continuous hexdump style switch). For example let's say I have jpg image in bytea column in samples table:

\copy (SELECT encode(file, 'hex') FROM samples LIMIT 1) TO
    '/home/grzegorz/Desktop/image.hex'

$ xxd -p -r image.hex > image.jpg

As I checked it works in practice.

Solution 2

Try this:

 COPY (SELECT yourbyteacolumn FROM yourtable WHERE <add your clauses here> ...) TO 'youroutputfile' (FORMAT binary)

Solution 3

Here's the simplest thing I could come up with:

psql -qAt "select encode(file,'base64') from files limit 1" | base64 -d

The -qAt is important as it strips off any formatting of the output. These options are available inside the psql shell, too.

Solution 4

If you have a lot of data to download then you can get the lines first and then iterate through each one writing the bytea field to file.

$resource = pg_connect('host=localhost port=5432 dbname=website user=super password=************');

// grab all the user IDs
$userResponse = pg_query('select distinct(r.id) from resource r
                        join connection c on r.id = c.resource_id_from
                        join resource rfile on c.resource_id_to = rfile.id and         rfile.resource_type_id = 10
                        join file f on rfile.id = f.resource_id
                        join file_type ft on f.file_type_id = ft.id
                        where r.resource_type_id = 38');

// need to work through one by one to handle data
while($user = pg_fetch_array($userResponse)){
    $user_id = $user['id'];
    $query = 'select r.id, f.data, rfile.resource_type_id, ft.extension from resource r
                        join connection c on r.id = c.resource_id_from
                        join resource rfile on c.resource_id_to = rfile.id and rfile.resource_type_id = 10
                        join file f on rfile.id = f.resource_id
                        join file_type ft on f.file_type_id = ft.id
                        where r.resource_type_id = 38 and r.id = ' . $user_id;

    $fileResponse = pg_query($query);
    $fileData = pg_fetch_array($fileResponse);
    $data = pg_unescape_bytea($fileData['data']);
    $extension = $fileData['extension'];
    $fileId = $fileData['id'];
    $filename = $fileId . '.' . $extension;
    $fileHandle = fopen($filename, 'w');
    fwrite($fileHandle, $data);
    fclose($fileHandle);
}

Solution 5

base64

psql -Aqt -c "SELECT encode(content, 'base64') FROM ..." | base64 -d > file

xxd

psql -Aqt -c "SELECT encode(content, 'hex') FROM ..." | xxd -p -r > file

View more solutions

Comments

[Admin]

Hi, can you provide me some example code to download bytea to file at the application level. Thanks!

[Denis de Bernardy]

Err.. You should have that code to display the files. Just save the stream instead.

[Marcel]

Is it possible to not rely upon xxd for this? Isn't there a purely postgresql based solution to achieve the copy to?

[Sjon]

This works; but the output needs additional processing. See Binary Format

[user3132194]

@Marcel You could use while read -N2 code; do printf "\x$code"; done <image.hex >image.jpg instead xxd. It is not pure postgresql, but you need only bash for this. They say xxd is a part of vim package.

[Evan Carroll]

You would have to trim off the binary headers and the footers. I downvoted, the answer isn't complete.

[pdw]

Instead of 'hex', you can use 'base64'. The file will be smaller and the base64 utility is more common than xxd.

[Nux]

This doesn't work at least not on PG SQL 9.3. You need to strip leading x from the string and use hex2bin to get actual binary data.

[Johan]

Welcome to Stack Overflow! While this code snippet may be the solution, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

[Chris Cogdon]

use psql -qt... which will output just the field itself, then you can omit the \copy

[Manohar Reddy Poreddy]

@DenisdeBernardy question is about download, not display the file.

[Denis de Bernardy]

@ManoharReddyPoreddy: Indeed. It's been a while since I wrote this but the reply was for the comment immediately above it. And I stand by my answer: if OP has (or had, since this was 8 years ago) code somewhere that writes browser headers and sends the raw file data to the browser, then fiddle with that code so it can write to a different stream instead -- such as a file.

[Manohar Reddy Poreddy]

@DenisdeBernardy please share the code that works, this way it is more useful, as you can see we are all struggling to get this work correctly.

[Denis de Bernardy]

@ManoharReddyPoreddy: I'm sorry, I unfortunately don't have code that works for this, because I've never made the extremely dubious decision of storing binary data into a database. My point is that, assuming you have this issue, you already have code that works. To output the code as a file for a backup or something, reuse the code you've already written to display it in the browser. And if you're browsing this page because you're struggling to write the code to display the file, then you should revisit your database schema before it's too late and store an s3 uri instead.

[Manohar Reddy Poreddy]

@DenisdeBernardy, s3 or other is not an option for client. I think I got all the answers from the way you write your replies.

[J. Random Coder]

This solution worked for me when I added -c which according to the inline help tells psql to "run only single command (SQL or internal) and exit"

[postgresnewbie]

That's very nice solution btw! Thanks for your effort! This way we can get multiple rows instead of single one.