am i misunderstanding this~ Why is COPY changing the output? (PGPLSQL)

Question

I'm very close to solving a big problem that i'm having at the moment and this is the last bit. I just don't know why the output is different to what's in the database when i need it EXACTLY the same.

Reading the data before entering the database:

[NULL][NULL][NULL][SO][etc.......]

Reading the data from COPY to text file:

\\000\\000\\000\\016[etc...} (it matches, basically)

Reading the data after COPY using binary format

PGCOPY
ÿ
[NULL][NULL][NULL][EOT][etc.......] (first line changes a fair bit)
(rest of the data stays exactly the same.)
˜ÿÿ

The postgresql query being run for test's sake:

COPY (SELECT byteacolumn FROM tablename WHERE id = 1) TO 'C:\path\file' (format:Binary);

So using the binary format gives me almost what i need but not quite. I could botch to ignore the added lines, but i wouldn't know what the first line of data should be.

TL;DR: COPY is adding lines and changing the first row of my data. How do i make it stop? :(

Answer 1

The binary COPY format is really only designed to be consumed by a COPY FROM command, so it contains a lot of metadata to allow Postgres to interpret it.

After the first two lines, the next 9 bytes are also part of the header. The next 2 bytes give the number of fields in the following record, and the next 4 bytes give the number of bytes in the following field. Only then does the actual data begin.

The full details of the format can be found in the documentation, but be aware that they could change in a future release.

(However, assuming this is the same problem you were asking about here, I think this is the wrong way to go about it. You were on the right track with lo_import/lo_export.)