Home Explore Blog CI



postgresql

15th chunk of `doc/src/sgml/ref/copy.sgml`
fdee6f61499fbdca2e1cc027aea79e4668728793bb292dc50000000100000cad

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

Title: Binary Format Details (Cont.) and Examples of COPY Usage
Summary
This section elaborates on determining the binary format for tuple data by referencing the *send and *recv functions in the PostgreSQL source code. It mentions how OIDs are handled (if included) and then explains the structure of the file trailer (a 16-bit integer word containing -1). It also emphasizes the importance of error checking to ensure data synchronization. Then it provides several examples of using the COPY command, including copying data with a delimiter, copying from a file, copying with a WHERE clause, copying to a compressed file, and examples of data suitable for copying from STDIN and in binary format.