<!--
doc/src/sgml/ref/copy.sgml
PostgreSQL documentation
-->

<refentry id="sql-copy">
 <indexterm zone="sql-copy">
  <primary>COPY</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>COPY</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>COPY</refname>
  <refpurpose>copy data between a file and a table</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
COPY <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
    FROM { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDIN }
    [ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
    [ WHERE <replaceable class="parameter">condition</replaceable> ]

COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
    TO { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDOUT }
    [ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]

<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>

    FORMAT <replaceable class="parameter">format_name</replaceable>
    FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
    DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>'
    NULL '<replaceable class="parameter">null_string</replaceable>'
    DEFAULT '<replaceable class="parameter">default_string</replaceable>'
    HEADER [ <replaceable class="parameter">boolean</replaceable> | MATCH ]
    QUOTE '<replaceable class="parameter">quote_character</replaceable>'
    ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
    FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
    FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
    FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
    ON_ERROR <replaceable class="parameter">error_action</replaceable>
    REJECT_LIMIT <replaceable class="parameter">maxerror</replaceable>
    ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
    LOG_VERBOSITY <replaceable class="parameter">verbosity</replaceable>
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>COPY</command> moves data between
   <productname>PostgreSQL</productname> tables and standard file-system
   files. <command>COPY TO</command> copies the contents of a table
   <emphasis>to</emphasis> a file, while <command>COPY FROM</command> copies
   data <emphasis>from</emphasis> a file to a table (appending the data to
   whatever is in the table already).  <command>COPY TO</command>
   can also copy the results of a <command>SELECT</command> query.
  </para>

  <para>
   If a column list is specified, <command>COPY TO</command> copies only
   the data in the specified columns to the file.  For <command>COPY
   FROM</command>, each field in the file is inserted, in order, into the
   specified column.  Table columns not specified in the <command>COPY
   FROM</command> column list will receive their default values.
  </para>

  <para>
   <command>COPY</command> with a file name instructs the
   <productname>PostgreSQL</productname> server to directly read from
   or write to a file. The file must be accessible by the
   <productname>PostgreSQL</productname> user (the user ID the server
   runs as) and the name must be specified from the viewpoint of the
   server. When <literal>PROGRAM</literal> is specified, the server
   executes the given command and reads from the standard output of the
   program, or writes to the standard input of the