15

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?

Carcamano
  • 1,153
  • 2
  • 11
  • 24
  • Good question, and I do not know the answer. Maybe, however, `-A` and `-t` will help: `--no-align` and `--tuples-only` respectively. – joepd Dec 17 '15 at 13:22
  • 1
    I tried both options alone and combined, but the header was still there. Maybe those apply only to text output? – Carcamano Dec 17 '15 at 13:36
  • Sorry, it was just a suggestion. Now that has been ruled out, we'll have to wait for more knowledgable folks. – joepd Dec 17 '15 at 13:37

2 Answers2

21

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/|
Carcamano
  • 1,153
  • 2
  • 11
  • 24
  • 3
    God bless you! That is the simplest answer to something that should be simple but is awfully complicated! – MarHoff Feb 22 '17 at 08:58
  • 2
    You could use `| while read -N2 code; do printf "\x$code"; done >` instead of `| xxd -p -r >`, it is pure bash. They say xxd is a part of vim package. – user3132194 Mar 28 '17 at 11:45
2

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).

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63