Menu
I have a bytea column in PostgreSQL DB which holds PDF files.
How can I export that file using psql?
I tried:
psql -U <USER> -h <HOST> -p <PORT> -d <DB> -c "\copy (select <column> from <table> where <column> = <id>) to STDOUT with BINARY;" > output.pdf
That saves the file and I can open it in a PDF reader. But when I check the file with hexdump -C output.pdf | head, I see it has a header starting with PGCOPY.
How can export that file without PGCOPY header?
829
PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table.
COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.
The easiest but the most efficient way to export data from a Postgres table to a CSV file is by using the COPY command. COPY command generates a CSV file on the Database Server. You can export the entire table or the results of a query to a CSV file with the COPY TO command.
The delimiter is a string used as the delimiter for splitting. The position argument sets the part of the string that is to be returned, starting from 1. The argument must have a positive integer as its value.
I got it using Postgre's encode() to hex and bash xxd to decode from hex:
psql -U <USER> -h <HOST> -p <PORT> -d <DB> -c "\copy (SELECT encode(<column>, 'hex') from <table> where <column> = <id>) to STDOUT" | xxd -p -r > output
File looks ok:
$ hexdump -C output | head -n 5
00000000 25 50 44 46 2d 31 2e 36 0d 25 e2 e3 cf d3 0d 0a |%PDF-1.6.%......|
00000010 38 37 20 30 20 6f 62 6a 0d 3c 3c 2f 4c 69 6e 65 |87 0 obj.<</Line|
00000020 61 72 69 7a 65 64 20 31 2f 4c 20 31 30 32 33 32 |arized 1/L 10232|
00000030 32 35 2f 4f 20 38 39 2f 45 20 31 35 36 35 30 36 |25/O 89/E 156506|
00000040 2f 4e 20 31 37 2f 54 20 31 30 32 32 38 30 36 2f |/N 17/T 1022806/|
Binary COPY OUT files are only intended for consumption by COPY IN commands. There is no way to prevent Postgres from writing the file/row/field headers.
You could try to strip them off yourself - either after creating the file, or by piping it straight into dd using the COPY TO PROGRAM statement - though keep in mind that the headers could potentially change in future releases of Postgres.
As far as I'm aware, the only mechanism provided by Postgres to write binary files is lo_export, though you'll have to jump through a few hoops to convert your data to the Large Object format.
Alternatively, you can write your own function in an untrusted procedural language (PL/PerlU or PL/PythonU).
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
