Parsing COPY ... with BINARY results

Question

I use this:

COPY (select field1, field2, field3 from the table) TO 'C: // Program Files / PostgreSql // 8.4 // data // output.dat' WITH BINARY

To export some fields to a file, one of them is the ByteA field. Now I need to read the file using a specially created program.

How can I parse this file?

Answer 1

The general format of the file generated by COPY...BINARY is explained in the documentation , and it is not trivial.

bytea content is most easily processed as it is not encoded.

Each other data type has its own encoding rules, which are not described in the documentation, but in the source code. From the doc:

To determine the appropriate binary format for the actual tuple data, you should refer to the PostgreSQL source, in particular * recv functions for each type of column data (usually these functions are in the src / backend / utils / adt / source distribution directory).

Answer 2

It might be easier to use a text format rather than binary (so just remove WITH BINARY ). The text format has the best documentation and is designed for better interoperability. The binary format is more suitable for moving between postgres installations, and even there they have version incompatibility.

The text format will write the bytea field as if it were text, and encode any non-printable characters with octal representation \nnn (except for a few special cases that it encodes with C \x style patterns such as \n and \t and t .d.). They are listed in the COPY documentation.

The only caveat to this is that you must be absolutely sure that the character encoding you use is the same when saving the file while reading. To make sure the printed characters match the same numbers. I stick with SQL_ASCII as this makes the process easier.