doc/src/sgml/ref/copy.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6
   7 <refentry id="SQL-COPY">
   8  <refmeta>
   9   <refentrytitle id="sql-copy-title">COPY</refentrytitle>
  10   <manvolnum>7</manvolnum>
  11   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  12  </refmeta>
  13
  14  <refnamediv>
  15   <refname>COPY</refname>
  16   <refpurpose>copy data between a file and a table</refpurpose>
  17  </refnamediv>
  18
  19  <indexterm zone="sql-copy">
  20   <primary>COPY</primary>
  21  </indexterm>
  22
  23  <refsynopsisdiv>
  24 <synopsis>
  25 COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
  26     FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
  27     [ [ WITH ]
  28           [ BINARY ]
  29           [ OIDS ]
  30           [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ]
  31           [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ]
  32           [ CSV [ HEADER ]
  33                 [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ]
  34                 [ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]
  35                 [ FORCE NOT NULL <replaceable class="parameter">column</replaceable> [, ...] ]
  36
  37 COPY { <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
  38     TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
  39     [ [ WITH ]
  40           [ BINARY ]
  41           [ OIDS ]
  42           [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ]
  43           [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ]
  44           [ CSV [ HEADER ]
  45                 [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ]
  46                 [ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]
  47                 [ FORCE QUOTE <replaceable class="parameter">column</replaceable> [, ...] ]
  48 </synopsis>
  49  </refsynopsisdiv>
  50
  51  <refsect1>
  52   <title>Description</title>
  53
  54   <para>
  55    <command>COPY</command> moves data between
  56    <productname>PostgreSQL</productname> tables and standard file-system
  57    files. <command>COPY TO</command> copies the contents of a table
  58    <emphasis>to</> a file, while <command>COPY FROM</command> copies
  59    data <emphasis>from</> a file to a table (appending the data to
  60    whatever is in the table already).  <command>COPY TO</command>
  61    can also copy the results of a <command>SELECT</> query.
  62   </para>
  63
  64   <para>
  65    If a list of columns is specified, <command>COPY</command> will
  66    only copy the data in the specified columns to or from the file.
  67    If there are any columns in the table that are not in the column list,
  68    <command>COPY FROM</command> will insert the default values for
  69    those columns.
  70   </para>
  71
  72   <para>
  73    <command>COPY</command> with a file name instructs the
  74    <productname>PostgreSQL</productname> server to directly read from
  75    or write to a file. The file must be accessible to the server and
  76    the name must be specified from the viewpoint of the server. When
  77    <literal>STDIN</literal> or <literal>STDOUT</literal> is
  78    specified, data is transmitted via the connection between the
  79    client and the server.
  80   </para>
  81  </refsect1>
  82
  83  <refsect1>
  84   <title>Parameters</title>
  85
  86   <variablelist>
  87    <varlistentry>
  88     <term><replaceable class="parameter">tablename</replaceable></term>
  89     <listitem>
  90      <para>
  91       The name (optionally schema-qualified) of an existing table.
  92      </para>
  93     </listitem>
  94    </varlistentry>
  95
  96    <varlistentry>
  97     <term><replaceable class="parameter">column</replaceable></term>
  98      <listitem>
  99      <para>
 100       An optional list of columns to be copied.  If no column list is
 101       specified, all columns of the table will be copied.
 102      </para>
 103     </listitem>
 104    </varlistentry>
 105
 106    <varlistentry>
 107     <term><replaceable class="parameter">query</replaceable></term>
 108     <listitem>
 109      <para>
 110       A <xref linkend="sql-select" endterm="sql-select-title"> or
 111       <xref linkend="sql-values" endterm="sql-values-title"> command
 112       whose results are to be copied.
 113       Note that parentheses are required around the query.
 114      </para>
 115     </listitem>
 116    </varlistentry>
 117
 118    <varlistentry>
 119     <term><replaceable class="parameter">filename</replaceable></term>
 120     <listitem>
 121      <para>
 122       The absolute path name of the input or output file.  Windows users
 123       might need to use an <literal>E''</> string and double backslashes
 124       used as path separators.
 125      </para>
 126     </listitem>
 127    </varlistentry>
 128
 129    <varlistentry>
 130     <term><literal>STDIN</literal></term>
 131     <listitem>
 132      <para>
 133       Specifies that input comes from the client application.
 134      </para>
 135     </listitem>
 136    </varlistentry>
 137
 138    <varlistentry>
 139     <term><literal>STDOUT</literal></term>
 140     <listitem>
 141      <para>
 142       Specifies that output goes to the client application.
 143      </para>
 144     </listitem>
 145    </varlistentry>
 146
 147    <varlistentry>
 148     <term><literal>BINARY</literal></term>
 149     <listitem>
 150      <para>
 151       Causes all data to be stored or read in binary format rather
 152       than as text. You cannot specify the <option>DELIMITER</option>,
 153       <option>NULL</option>, or <option>CSV</> options in binary mode.
 154      </para>
 155     </listitem>
 156    </varlistentry>
 157
 158    <varlistentry>
 159     <term><literal>OIDS</literal></term>
 160     <listitem>
 161      <para>
 162       Specifies copying the OID for each row.  (An error is raised if
 163       <literal>OIDS</literal> is specified for a table that does not
 164       have OIDs, or in the case of copying a <replaceable
 165       class="parameter">query</replaceable>.)
 166      </para>
 167     </listitem>
 168    </varlistentry>
 169
 170    <varlistentry>
 171     <term><replaceable class="parameter">delimiter</replaceable></term>
 172     <listitem>
 173      <para>
 174       The single ASCII character that separates columns within each row
 175       (line) of the file.  The default is a tab character in text mode,
 176       a comma in <literal>CSV</> mode.
 177      </para>
 178     </listitem>
 179    </varlistentry>
 180
 181    <varlistentry>
 182     <term><replaceable class="parameter">null string</replaceable></term>
 183     <listitem>
 184      <para>
 185       The string that represents a null value. The default is
 186       <literal>\N</literal> (backslash-N) in text mode, and a empty
 187       value with no quotes in <literal>CSV</> mode. You might prefer an
 188       empty string even in text mode for cases where you don't want to
 189       distinguish nulls from empty strings.
 190      </para>
 191
 192      <note>
 193       <para>
 194        When using <command>COPY FROM</command>, any data item that matches
 195        this string will be stored as a null value, so you should make
 196        sure that you use the same string as you used with
 197        <command>COPY TO</command>.
 198       </para>
 199      </note>
 200
 201     </listitem>
 202    </varlistentry>
 203
 204    <varlistentry>
 205     <term><literal>CSV</literal></term>
 206     <listitem>
 207      <para>
 208       Selects Comma Separated Value (<literal>CSV</>) mode.
 209      </para>
 210     </listitem>
 211    </varlistentry>
 212
 213    <varlistentry>
 214     <term><literal>HEADER</literal></term>
 215     <listitem>
 216      <para>
 217       Specifies that the file contains a header line with the names of each
 218       column in the file.  On output, the first line contains the column
 219       names from the table, and on input, the first line is ignored.
 220      </para>
 221     </listitem>
 222    </varlistentry>
 223
 224    <varlistentry>
 225     <term><replaceable class="parameter">quote</replaceable></term>
 226     <listitem>
 227      <para>
 228       Specifies the ASCII quotation character in <literal>CSV</> mode.
 229       The default is double-quote.
 230      </para>
 231     </listitem>
 232    </varlistentry>
 233
 234    <varlistentry>
 235     <term><replaceable class="parameter">escape</replaceable></term>
 236     <listitem>
 237      <para>
 238       Specifies the ASCII character that should appear before a
 239       <literal>QUOTE</> data character value in <literal>CSV</> mode.
 240       The default is the <literal>QUOTE</> value (usually double-quote).
 241      </para>
 242     </listitem>
 243    </varlistentry>
 244
 245    <varlistentry>
 246     <term><literal>FORCE QUOTE</></term>
 247     <listitem>
 248      <para>
 249       In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be
 250       used for all non-<literal>NULL</> values in each specified column.
 251       <literal>NULL</> output is never quoted.
 252      </para>
 253     </listitem>
 254    </varlistentry>
 255
 256    <varlistentry>
 257     <term><literal>FORCE NOT NULL</></term>
 258     <listitem>
 259      <para>
 260       In <literal>CSV</> <command>COPY FROM</> mode, process each
 261       specified column as though it were quoted and hence not a
 262       <literal>NULL</> value. For the default null string in
 263       <literal>CSV</> mode (<literal>''</>), this causes missing
 264       values to be input as zero-length strings.
 265      </para>
 266     </listitem>
 267    </varlistentry>
 268
 269   </variablelist>
 270  </refsect1>
 271
 272  <refsect1>
 273   <title>Outputs</title>
 274
 275   <para>
 276    On successful completion, a <command>COPY</> command returns a command
 277    tag of the form
 278 <screen>
 279 COPY <replaceable class="parameter">count</replaceable>
 280 </screen>
 281    The <replaceable class="parameter">count</replaceable> is the number
 282    of rows copied.
 283   </para>
 284  </refsect1>
 285
 286  <refsect1>
 287   <title>Notes</title>
 288
 289    <para>
 290     <command>COPY</command> can only be used with plain tables, not
 291     with views.  However, you can write <literal>COPY (SELECT * FROM
 292     <replaceable class="parameter">viewname</replaceable>) TO ...</literal>.
 293    </para>
 294
 295    <para>
 296     The <literal>BINARY</literal> key word causes all data to be
 297     stored/read as binary format rather than as text.  It is
 298     somewhat faster than the normal text mode, but a binary-format
 299     file is less portable across machine architectures and
 300     <productname>PostgreSQL</productname> versions.
 301    </para>
 302
 303    <para>
 304     You must have select privilege on the table
 305     whose values are read by <command>COPY TO</command>, and
 306     insert privilege on the table into which values
 307     are inserted by <command>COPY FROM</command>.  It is sufficient
 308     to have column privileges on the column(s) listed in the command.
 309    </para>
 310
 311    <para>
 312     Files named in a <command>COPY</command> command are read or written
 313     directly by the server, not by the client application. Therefore,
 314     they must reside on or be accessible to the database server machine,
 315     not the client. They must be accessible to and readable or writable
 316     by the <productname>PostgreSQL</productname> user (the user ID the
 317     server runs as), not the client. <command>COPY</command> naming a
 318     file is only allowed to database superusers, since it allows reading
 319     or writing any file that the server has privileges to access.
 320    </para>
 321
 322    <para>
 323     Do not confuse <command>COPY</command> with the
 324     <application>psql</application> instruction
 325     <command>\copy</command>. <command>\copy</command> invokes
 326     <command>COPY FROM STDIN</command> or <command>COPY TO
 327     STDOUT</command>, and then fetches/stores the data in a file
 328     accessible to the <application>psql</application> client. Thus,
 329     file accessibility and access rights depend on the client rather
 330     than the server when <command>\copy</command> is used.
 331    </para>
 332
 333    <para>
 334     It is recommended that the file name used in <command>COPY</command>
 335     always be specified as an absolute path. This is enforced by the
 336     server in the case of <command>COPY TO</command>, but for
 337     <command>COPY FROM</command> you do have the option of reading from
 338     a file specified by a relative path. The path will be interpreted
 339     relative to the working directory of the server process (normally
 340     the cluster's data directory), not the client's working directory.
 341    </para>
 342
 343    <para>
 344     <command>COPY FROM</command> will invoke any triggers and check
 345     constraints on the destination table. However, it will not invoke rules.
 346    </para>
 347
 348    <para>
 349     <command>COPY</command> input and output is affected by
 350     <varname>DateStyle</varname>. To ensure portability to other
 351     <productname>PostgreSQL</productname> installations that might use
 352     non-default <varname>DateStyle</varname> settings,
 353     <varname>DateStyle</varname> should be set to <literal>ISO</> before
 354     using <command>COPY TO</>.  It is also a good idea to avoid dumping
 355     data with <varname>IntervalStyle</varname> set to
 356     <literal>sql_standard</>, because negative interval values might be
 357     misinterpreted by a server that has a different setting for
 358     <varname>IntervalStyle</varname>.
 359    </para>
 360
 361    <para>
 362     Input data is interpreted according to the current client encoding,
 363     and output data is encoded in the the current client encoding, even
 364     if the data does not pass through the client but is read from or
 365     written to a file.
 366    </para>
 367
 368    <para>
 369     <command>COPY</command> stops operation at the first error. This
 370     should not lead to problems in the event of a <command>COPY
 371     TO</command>, but the target table will already have received
 372     earlier rows in a <command>COPY FROM</command>. These rows will not
 373     be visible or accessible, but they still occupy disk space. This might
 374     amount to a considerable amount of wasted disk space if the failure
 375     happened well into a large copy operation. You might wish to invoke
 376     <command>VACUUM</command> to recover the wasted space.
 377    </para>
 378
 379  </refsect1>
 380
 381  <refsect1>
 382   <title>File Formats</title>
 383
 384   <refsect2>
 385    <title>Text Format</title>
 386
 387    <para>
 388     When <command>COPY</command> is used without the <literal>BINARY</literal>
 389     or <literal>CSV</> options,
 390     the data read or written is a text file with one line per table row.
 391     Columns in a row are separated by the delimiter character.
 392     The column values themselves are strings generated by the
 393     output function, or acceptable to the input function, of each
 394     attribute's data type.  The specified null string is used in
 395     place of columns that are null.
 396     <command>COPY FROM</command> will raise an error if any line of the
 397     input file contains more or fewer columns than are expected.
 398     If <literal>OIDS</literal> is specified, the OID is read or written as the first column,
 399     preceding the user data columns.
 400    </para>
 401
 402    <para>
 403     End of data can be represented by a single line containing just
 404     backslash-period (<literal>\.</>).  An end-of-data marker is
 405     not necessary when reading from a file, since the end of file
 406     serves perfectly well; it is needed only when copying data to or from
 407     client applications using pre-3.0 client protocol.
 408    </para>
 409
 410    <para>
 411     Backslash characters (<literal>\</>) can be used in the
 412     <command>COPY</command> data to quote data characters that might
 413     otherwise be taken as row or column delimiters. In particular, the
 414     following characters <emphasis>must</> be preceded by a backslash if
 415     they appear as part of a column value: backslash itself,
 416     newline, carriage return, and the current delimiter character.
 417    </para>
 418
 419    <para>
 420     The specified null string is sent by <command>COPY TO</command> without
 421     adding any backslashes; conversely, <command>COPY FROM</command> matches
 422     the input against the null string before removing backslashes.  Therefore,
 423     a null string such as <literal>\N</literal> cannot be confused with
 424     the actual data value <literal>\N</literal> (which would be represented
 425     as <literal>\\N</literal>).
 426    </para>
 427
 428    <para>
 429     The following special backslash sequences are recognized by
 430     <command>COPY FROM</command>:
 431
 432    <informaltable>
 433     <tgroup cols="2">
 434      <thead>
 435       <row>
 436        <entry>Sequence</entry>
 437        <entry>Represents</entry>
 438       </row>
 439      </thead>
 440
 441      <tbody>
 442       <row>
 443        <entry><literal>\b</></entry>
 444        <entry>Backspace (ASCII 8)</entry>
 445       </row>
 446       <row>
 447        <entry><literal>\f</></entry>
 448        <entry>Form feed (ASCII 12)</entry>
 449       </row>
 450       <row>
 451        <entry><literal>\n</></entry>
 452        <entry>Newline (ASCII 10)</entry>
 453       </row>
 454       <row>
 455        <entry><literal>\r</></entry>
 456        <entry>Carriage return (ASCII 13)</entry>
 457       </row>
 458       <row>
 459        <entry><literal>\t</></entry>
 460        <entry>Tab (ASCII 9)</entry>
 461       </row>
 462       <row>
 463        <entry><literal>\v</></entry>
 464        <entry>Vertical tab (ASCII 11)</entry>
 465       </row>
 466       <row>
 467        <entry><literal>\</><replaceable>digits</></entry>
 468        <entry>Backslash followed by one to three octal digits specifies
 469        the character with that numeric code</entry>
 470       </row>
 471       <row>
 472        <entry><literal>\x</><replaceable>digits</></entry>
 473        <entry>Backslash <literal>x</> followed by one or two hex digits specifies
 474        the character with that numeric code</entry>
 475       </row>
 476      </tbody>
 477     </tgroup>
 478    </informaltable>
 479
 480     Presently, <command>COPY TO</command> will never emit an octal or
 481     hex-digits backslash sequence, but it does use the other sequences
 482     listed above for those control characters.
 483    </para>
 484
 485    <para>
 486     Any other backslashed character that is not mentioned in the above table
 487     will be taken to represent itself.  However, beware of adding backslashes
 488     unnecessarily, since that might accidentally produce a string matching the
 489     end-of-data marker (<literal>\.</>) or the null string (<literal>\N</> by
 490     default).  These strings will be recognized before any other backslash
 491     processing is done.
 492    </para>
 493
 494    <para>
 495     It is strongly recommended that applications generating <command>COPY</command> data convert
 496     data newlines and carriage returns to the <literal>\n</> and
 497     <literal>\r</> sequences respectively.  At present it is
 498     possible to represent a data carriage return by a backslash and carriage
 499     return, and to represent a data newline by a backslash and newline.
 500     However, these representations might not be accepted in future releases.
 501     They are also highly vulnerable to corruption if the <command>COPY</command> file is
 502     transferred across different machines (for example, from Unix to Windows
 503     or vice versa).
 504    </para>
 505
 506    <para>
 507     <command>COPY TO</command> will terminate each row with a Unix-style
 508     newline (<quote><literal>\n</></>).  Servers running on Microsoft Windows instead
 509     output carriage return/newline (<quote><literal>\r\n</></>), but only for
 510     <command>COPY</> to a server file; for consistency across platforms,
 511     <command>COPY TO STDOUT</> always sends <quote><literal>\n</></>
 512     regardless of server platform.
 513     <command>COPY FROM</command> can handle lines ending with newlines,
 514     carriage returns, or carriage return/newlines.  To reduce the risk of
 515     error due to un-backslashed newlines or carriage returns that were
 516     meant as data, <command>COPY FROM</command> will complain if the line
 517     endings in the input are not all alike.
 518    </para>
 519   </refsect2>
 520
 521   <refsect2>
 522    <title>CSV Format</title>
 523
 524    <para>
 525     This format is used for importing and exporting the Comma
 526     Separated Value (<literal>CSV</>) file format used by many other
 527     programs, such as spreadsheets. Instead of the escaping used by
 528     <productname>PostgreSQL</productname>'s standard text mode, it
 529     produces and recognizes the common CSV escaping mechanism.
 530    </para>
 531
 532    <para>
 533     The values in each record are separated by the <literal>DELIMITER</>
 534     character. If the value contains the delimiter character, the
 535     <literal>QUOTE</> character, the <literal>NULL</> string, a carriage
 536     return, or line feed character, then the whole value is prefixed and
 537     suffixed by the <literal>QUOTE</> character, and any occurrence
 538     within the value of a <literal>QUOTE</> character or the
 539     <literal>ESCAPE</> character is preceded by the escape character.
 540     You can also use <literal>FORCE QUOTE</> to force quotes when outputting
 541     non-<literal>NULL</> values in specific columns.
 542    </para>
 543
 544    <para>
 545     The <literal>CSV</> format has no standard way to distinguish a
 546     <literal>NULL</> value from an empty string.
 547     <productname>PostgreSQL</>'s <command>COPY</> handles this by
 548     quoting. A <literal>NULL</> is output as the <literal>NULL</>
 549     string and is not quoted, while a data value matching the
 550     <literal>NULL</> string is quoted. Therefore, using the default
 551     settings, a <literal>NULL</> is written as an unquoted empty
 552     string, while an empty string is written with double quotes
 553     (<literal>""</>). Reading values follows similar rules. You can
 554     use <literal>FORCE NOT NULL</> to prevent <literal>NULL</> input
 555     comparisons for specific columns.
 556    </para>
 557
 558    <para>
 559     Because backslash is not a special character in the <literal>CSV</>
 560     format, <literal>\.</>, the end-of-data marker, could also appear
 561     as a data value.  To avoid any misinterpretation, a <literal>\.</>
 562     data value appearing as a lone entry on a line is automatically
 563     quoted on output, and on input, if quoted, is not interpreted as the
 564     end-of-data marker.  If you are loading a file created by another
 565     application that has a single unquoted column and might have a
 566     value of <literal>\.</>, you might need to quote that value in the
 567     input file.
 568    </para>
 569
 570    <note>
 571     <para>
 572      In <literal>CSV</> mode, all characters are significant. A quoted value
 573      surrounded by white space, or any characters other than
 574      <literal>DELIMITER</>, will include those characters. This can cause
 575      errors if  you import data from a system that pads <literal>CSV</>
 576      lines with white space out to some fixed width. If such a situation
 577      arises you might need to preprocess the <literal>CSV</> file to remove
 578      the trailing white space, before importing the data into
 579      <productname>PostgreSQL</>.
 580     </para>
 581    </note>
 582
 583    <note>
 584     <para>
 585      CSV mode will both recognize and produce CSV files with quoted
 586      values containing embedded carriage returns and line feeds. Thus
 587      the files are not strictly one line per table row like text-mode
 588      files.
 589     </para>
 590    </note>
 591
 592    <note>
 593     <para>
 594      Many programs produce strange and occasionally perverse CSV files,
 595      so the file format is more a convention than a standard. Thus you
 596      might encounter some files that cannot be imported using this
 597      mechanism, and <command>COPY</> might produce files that other
 598      programs cannot process.
 599     </para>
 600    </note>
 601
 602   </refsect2>
 603
 604   <refsect2>
 605    <title>Binary Format</title>
 606
 607    <para>
 608     The file format used for <command>COPY BINARY</command> changed in
 609     <productname>PostgreSQL</productname> 7.4. The new format consists
 610     of a file header, zero or more tuples containing the row data, and
 611     a file trailer. Headers and data are now in network byte order.
 612    </para>
 613
 614    <refsect3>
 615     <title>File Header</title>
 616
 617     <para>
 618      The file header consists of 15 bytes of fixed fields, followed
 619      by a variable-length header extension area.  The fixed fields are:
 620
 621     <variablelist>
 622      <varlistentry>
 623       <term>Signature</term>
 624       <listitem>
 625        <para>
 626 11-byte sequence <literal>PGCOPY\n\377\r\n\0</> &mdash; note that the zero byte
 627 is a required part of the signature.  (The signature is designed to allow
 628 easy identification of files that have been munged by a non-8-bit-clean
 629 transfer.  This signature will be changed by end-of-line-translation
 630 filters, dropped zero bytes, dropped high bits, or parity changes.)
 631        </para>
 632       </listitem>
 633      </varlistentry>
 634
 635      <varlistentry>
 636       <term>Flags field</term>
 637       <listitem>
 638        <para>
 639 32-bit integer bit mask to denote important aspects of the file format. Bits
 640 are numbered from 0 (<acronym>LSB</>) to 31 (<acronym>MSB</>).  Note that
 641 this field is stored in network byte order (most significant byte first),
 642 as are all the integer fields used in the file format.  Bits
 643 16-31 are reserved to denote critical file format issues; a reader
 644 should abort if it finds an unexpected bit set in this range. Bits 0-15
 645 are reserved to signal backwards-compatible format issues; a reader
 646 should simply ignore any unexpected bits set in this range. Currently
 647 only one flag bit is defined, and the rest must be zero:
 648         <variablelist>
 649          <varlistentry>
 650           <term>Bit 16</term>
 651           <listitem>
 652            <para>
 653             if 1, OIDs are included in the data; if 0, not
 654            </para>
 655           </listitem>
 656          </varlistentry>
 657         </variablelist>
 658        </para>
 659       </listitem>
 660      </varlistentry>
 661
 662      <varlistentry>
 663       <term>Header extension area length</term>
 664       <listitem>
 665        <para>
 666 32-bit integer, length in bytes of remainder of header, not including self.
 667 Currently, this is zero, and the first tuple follows
 668 immediately.  Future changes to the format might allow additional data
 669 to be present in the header.  A reader should silently skip over any header
 670 extension data it does not know what to do with.
 671        </para>
 672       </listitem>
 673      </varlistentry>
 674     </variablelist>
 675     </para>
 676
 677     <para>
 678 The header extension area is envisioned to contain a sequence of
 679 self-identifying chunks.  The flags field is not intended to tell readers
 680 what is in the extension area.  Specific design of header extension contents
 681 is left for a later release.
 682     </para>
 683
 684     <para>
 685      This design allows for both backwards-compatible header additions (add
 686      header extension chunks, or set low-order flag bits) and
 687      non-backwards-compatible changes (set high-order flag bits to signal such
 688      changes, and add supporting data to the extension area if needed).
 689     </para>
 690    </refsect3>
 691
 692    <refsect3>
 693     <title>Tuples</title>
 694     <para>
 695 Each tuple begins with a 16-bit integer count of the number of fields in the
 696 tuple.  (Presently, all tuples in a table will have the same count, but that
 697 might not always be true.)  Then, repeated for each field in the tuple, there
 698 is a 32-bit length word followed by that many bytes of field data.  (The
 699 length word does not include itself, and can be zero.)  As a special case,
 700 -1 indicates a NULL field value.  No value bytes follow in the NULL case.
 701     </para>
 702
 703     <para>
 704 There is no alignment padding or any other extra data between fields.
 705     </para>
 706
 707     <para>
 708 Presently, all data values in a <command>COPY BINARY</command> file are
 709 assumed to be in binary format (format code one).  It is anticipated that a
 710 future extension might add a header field that allows per-column format codes
 711 to be specified.
 712     </para>
 713
 714     <para>
 715 To determine the appropriate binary format for the actual tuple data you
 716 should consult the <productname>PostgreSQL</productname> source, in
 717 particular the <function>*send</> and <function>*recv</> functions for
 718 each column's data type (typically these functions are found in the
 719 <filename>src/backend/utils/adt/</filename> directory of the source
 720 distribution).
 721     </para>
 722
 723     <para>
 724 If OIDs are included in the file, the OID field immediately follows the
 725 field-count word.  It is a normal field except that it's not included
 726 in the field-count.  In particular it has a length word &mdash; this will allow
 727 handling of 4-byte vs. 8-byte OIDs without too much pain, and will allow
 728 OIDs to be shown as null if that ever proves desirable.
 729     </para>
 730    </refsect3>
 731
 732    <refsect3>
 733     <title>File Trailer</title>
 734
 735     <para>
 736      The file trailer consists of a 16-bit integer word containing -1.  This
 737      is easily distinguished from a tuple's field-count word.
 738     </para>
 739
 740     <para>
 741      A reader should report an error if a field-count word is neither -1
 742      nor the expected number of columns.  This provides an extra
 743      check against somehow getting out of sync with the data.
 744     </para>
 745    </refsect3>
 746   </refsect2>
 747  </refsect1>
 748
 749  <refsect1>
 750   <title>Examples</title>
 751
 752   <para>
 753    The following example copies a table to the client
 754    using the vertical bar (<literal>|</literal>) as the field delimiter:
 755 <programlisting>
 756 COPY country TO STDOUT WITH DELIMITER '|';
 757 </programlisting>
 758   </para>
 759
 760   <para>
 761    To copy data from a file into the <literal>country</> table:
 762 <programlisting>
 763 COPY country FROM '/usr1/proj/bray/sql/country_data';
 764 </programlisting>
 765   </para>
 766
 767   <para>
 768    To copy into a file just the countries whose names start with 'A':
 769 <programlisting>
 770 COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
 771 </programlisting>
 772   </para>
 773
 774   <para>
 775    Here is a sample of data suitable for copying into a table from
 776    <literal>STDIN</literal>:
 777 <programlisting>
 778 AF      AFGHANISTAN
 779 AL      ALBANIA
 780 DZ      ALGERIA
 781 ZM      ZAMBIA
 782 ZW      ZIMBABWE
 783 </programlisting>
 784    Note that the white space on each line is actually a tab character.
 785   </para>
 786
 787   <para>
 788    The following is the same data, output in binary format.
 789    The data is shown after filtering through the
 790    Unix utility <command>od -c</command>. The table has three columns;
 791    the first has type <type>char(2)</type>, the second has type <type>text</type>,
 792    and the third has type <type>integer</type>. All the rows have a null value
 793    in the third column.
 794 <programlisting>
 795 0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
 796 0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
 797 0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
 798 0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
 799 0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
 800 0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
 801 0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
 802 0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
 803 0000200   M   B   A   B   W   E 377 377 377 377 377 377
 804 </programlisting>
 805   </para>
 806  </refsect1>
 807
 808  <refsect1>
 809   <title>Compatibility</title>
 810
 811   <para>
 812    There is no <command>COPY</command> statement in the SQL standard.
 813   </para>
 814
 815   <para>
 816    The following syntax was used before <productname>PostgreSQL</>
 817    version 7.3 and is still supported:
 818
 819 <synopsis>
 820 COPY [ BINARY ] <replaceable class="parameter">tablename</replaceable> [ WITH OIDS ]
 821     FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
 822     [ [USING] DELIMITERS '<replaceable class="parameter">delimiter</replaceable>' ]
 823     [ WITH NULL AS '<replaceable class="parameter">null string</replaceable>' ]
 824
 825 COPY [ BINARY ] <replaceable class="parameter">tablename</replaceable> [ WITH OIDS ]
 826     TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
 827     [ [USING] DELIMITERS '<replaceable class="parameter">delimiter</replaceable>' ]
 828     [ WITH NULL AS '<replaceable class="parameter">null string</replaceable>' ]
 829 </synopsis>
 830   </para>
 831  </refsect1>
 832 </refentry>