To determine the appropriate binary format for the actual tuple data you
should consult the <productname>PostgreSQL</productname> source, in
particular the <function>*send</function> and <function>*recv</function> functions for
each column's data type (typically these functions are found in the
<filename>src/backend/utils/adt/</filename> directory of the source
distribution).
</para>
<para>
If OIDs are included in the file, the OID field immediately follows the
field-count word. It is a normal field except that it's not included in the
field-count. Note that oid system columns are not supported in current
versions of <productname>PostgreSQL</productname>.
</para>
</refsect3>
<refsect3>
<title>File Trailer</title>
<para>
The file trailer consists of a 16-bit integer word containing -1. This
is easily distinguished from a tuple's field-count word.
</para>
<para>
A reader should report an error if a field-count word is neither -1
nor the expected number of columns. This provides an extra
check against somehow getting out of sync with the data.
</para>
</refsect3>
</refsect2>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
The following example copies a table to the client
using the vertical bar (<literal>|</literal>) as the field delimiter:
<programlisting>
COPY country TO STDOUT (DELIMITER '|');
</programlisting>
</para>
<para>
To copy data from a file into the <literal>country</literal> table:
<programlisting>
COPY country FROM '/usr1/proj/bray/sql/country_data';
</programlisting>
</para>
<para>
To copy into a file just the countries whose names start with 'A':
<programlisting>
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
</programlisting>
</para>
<para>
To copy into a compressed file, you can pipe the output through an external
compression program:
<programlisting>
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';
</programlisting>
</para>
<para>
Here is a sample of data suitable for copying into a table from
<literal>STDIN</literal>:
<programlisting>
AF AFGHANISTAN
AL ALBANIA
DZ ALGERIA
ZM ZAMBIA
ZW ZIMBABWE
</programlisting>
Note that the white space on each line is actually a tab character.
</para>
<para>
The following is the same data, output in binary format.
The data is shown after filtering through the
Unix utility <command>od -c</command>. The table has three columns;
the first has type <type>char(2)</type>, the second has type <type>text</type>,
and the third has type <type>integer</type>. All the rows have a null value
in the third column.
<programlisting>
0000000 P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \0
0000020 \0 \0 \0 \0 003 \0 \0 \0 002 A F \0 \0 \0 013 A
0000040 F G H A N I S T A N 377 377 377 377 \0 003
0000060 \0 \0 \0 002 A L \0 \0 \0 007 A L B A N I
0000100 A 377 377 377 377 \0 003 \0 \0 \0 002 D Z \0 \0 \0
0000120 007 A L G E R I A 377 377 377 377 \0 003 \0 \0
0000140 \0 002 Z M \0 \0 \0