COPY(l)                                                   COPY(l)

NAME
       COPY - Copies data between files and tables

SYNOPSIS
       COPY [ BINARY ] table [ WITH OIDS ]
           FROM { 'filename' | stdin }
           [ USING DELIMITERS 'delimiter' ]
       COPY [ BINARY ] table [ WITH OIDS ]
           TO { 'filename' | stdout }
           [ USING DELIMITERS 'delimiter' ]

   INPUTS
       BINARY Changes  the  behavior of field formatting, forcing
              all data to be stored or  read  as  binary  objects
              rather than as text.

       table  The name of an existing table.

       WITH OIDS
              Copies the internal unique object id (OID) for each
              row.

       filename
              The absolute Unix pathname of the input  or  output
              file.

       stdin  Specifies that input comes from a pipe or terminal.

       stdout Specifies that output goes to a pipe or terminal.

       delimiter
              A character  that  delimits  the  input  or  output
              fields.

   OUTPUTS
       COPY   The copy completed successfully.

       ERROR: reason
              The  copy failed for the reason stated in the error
              message.

DESCRIPTION
       COPY moves data between Postgres tables and standard file-
       system  files.   COPY  instructs  the  Postgres backend to
       directly read from or write to a file. The  file  must  be
       directly visible to the backend and the name must be spec-
       ified from the viewpoint of the backend.  If stdin or std-
       out  are specified, data flows through the client frontend
       to the backend.

   NOTES
       The BINARY keyword will force all data to  be  stored/read
       as  binary  objects  rather  than  as text. It is somewhat
       faster than the normal copy command, but is not  generally
       portable,  and  the  files  generated are somewhat larger,
       although this factor  is  highly  dependent  on  the  data
       itself.  By default, a text copy uses a tab ("\t") charac-
       ter as a delimiter.  The delimiter may also be changed  to
       any  other  single character with the keyword phrase USING
       DELIMITERS. Characters in  data  fields  which  happen  to
       match the delimiter character will be quoted.

       You  must have select access on any table whose values are
       read by COPY, and either insert  or  update  access  to  a
       table  into  which values are being inserted by COPY.  The
       backend also needs appropriate Unix  permissions  for  any
       file read or written by COPY.

       The  keyword  phrase  USING  DELIMITERS specifies a single
       character to be used for all delimiters  between  columns.
       If  multiple  characters  are  specified  in the delimiter
       string, only the first character is used.

              Tip: Do not confuse COPY with the psql  instruction
              \copy.

       COPY  neither  invokes  rules nor acts on column defaults.
       It does invoke triggers, however.

       COPY stops operation at the first error. This  should  not
       lead to problems in the event of a COPY FROM, but the tar-
       get relation will, of course, be partially modified  in  a
       COPY TO.  VACUUM should be used to clean up after a failed
       copy.

       Because the Postgres backend's current  working  directory
       is  not  usually the same as the user's working directory,
       the result of copying to a file "foo" (without  additional
       path  information)  may  yield  unexpected results for the
       naive user. In this case, foo will wind up in $PGDATA/foo.
       In  general,  the  full pathname as it would appear to the
       backend server machine  should  be  used  when  specifying
       files to be copied.

       Files  used  as  arguments  to  COPY  must reside on or be
       accessible to the database server machine by being  either
       on local disks or on a networked file system.

       When  a  TCP/IP  connection from one machine to another is
       used, and a target file is specified, the target file will
       be  written  on  the  machine where the backend is running
       rather than the user's machine.

FILE FORMATS
   TEXT FORMAT
       When COPY TO is used without the BINARY option,  the  file
       generated  will have each row (instance) on a single line,
       with each column (attribute) separated  by  the  delimiter
       character.  Embedded delimiter characters will be preceded
       by a backslash character ("\"). The attribute values them-
       selves  are strings generated by the output function asso-
       ciated with each attribute type. The output function for a
       type  should  not try to generate the backslash character;
       this will be handled by COPY itself.

       The actual format for each instance is

       <attr1><separator><attr2><separator>...<separator><attrn><newline>

       The oid is placed on the beginning of  the  line  if  WITH
       OIDS is specified.

       If  COPY  is sending its output to standard output instead
       of a file, it will send  a  backslash("\")  and  a  period
       (".")  followed  immediately  by  a newline, on a separate
       line, when it is done. Similarly, if COPY is reading  from
       standard  input,  it  will  expect a backslash ("\") and a
       period (".") followed by a newline,  as  the  first  three
       characters  on a line to denote end-of-file. However, COPY
       will terminate (followed by the backend itself) if a  true
       EOF is encountered before this special end-of-file pattern
       is found.

       The backslash character has other special  meanings.  NULL
       attributes  are  represented  as "\N". A literal backslash
       character is represented as  two  consecutive  backslashes
       ("\\").  A literal tab character is represented as a back-
       slash and a tab. A literal  newline  character  is  repre-
       sented  as  a  backslash  and a newline. When loading text
       data not generated by Postgres, you will need  to  convert
       backslash characters ("\") to double-backslashes ("\\") to
       ensure that they are loaded properly.

   BINARY FORMAT
       In the case of COPY BINARY, the first four  bytes  in  the
       file  will be the number of instances in the file. If this
       number is zero, the COPY BINARY command  will  read  until
       end  of file is encountered. Otherwise, it will stop read-
       ing when this number of instances has been read.   Remain-
       ing data in the file will be ignored.

       The  format  for  each instance in the file is as follows.
       Note that this format must be followed exactly.   Unsigned
       four-byte  integer  quantities  are  called  uint32 in the
       table below.  "CONTENTS OF A  BINARY  COPY  FILE"  At  the
       start   of   the   fileuint32number   of   tuplesFor  each
       tupleuint32total length of tuple datauint32oid (if  speci-
       fied)uint32number                  of                 null
       attributes[uint32,...,uint32]attribute     numbers      of
       attributes, counting from 0-<tuple data>

   ALIGNMENT OF BINARY DATA
       On  Sun-3s,  2-byte  attributes  are  aligned  on two-byte
       boundaries, and all larger attributes are aligned on four-
       byte boundaries.  Character attributes are aligned on sin-
       gle-byte  boundaries.  On   most   other   machines,   all
       attributes  larger  than  1  byte are aligned on four-byte
       boundaries. Note that variable length attributes are  pre-
       ceded  by  the  attribute's length; arrays are simply con-
       tiguous streams of the array element type.

USAGE
       The following example copies a table to  standard  output,
       using a vertical bar ("|") as the field delimiter:

       COPY country TO stdout USING DELIMITERS '|';

       To copy data from a Unix file into a table "country":

       COPY country FROM '/usr1/proj/bray/sql/country_data';

       Here is a sample of data suitable for copying into a table
       from stdin (so it has the termination sequence on the last
       line):

          AF      AFGHANISTAN
          AL      ALBANIA
          DZ      ALGERIA
          ...
          ZM      ZAMBIA
          ZW      ZIMBABWE
          \.

       The  same  data,  output  in binary format on a Linux/i586
       machine.  The data is shown after  filtering  through  the
       Unix  utility od -c. The table has three fields; the first
       is char(2) and the second is text. All  the  rows  have  a
       null  value  in  the  third field.  Notice how the char(2)
       field is padded with nulls to  four  bytes  and  the  text
       field is preceded by its length:

          355  \0  \0  \0 027  \0  \0  \0 001  \0  \0  \0 002  \0  \0  \0
          006  \0  \0  \0   A   F  \0  \0 017  \0  \0  \0   A   F   G   H
            A   N   I   S   T   A   N 023  \0  \0  \0 001  \0  \0  \0 002
           \0  \0  \0 006  \0  \0  \0   A   L  \0  \0  \v  \0  \0  \0   A
            L   B   A   N   I   A 023  \0  \0  \0 001  \0  \0  \0 002  \0
           \0  \0 006  \0  \0  \0   D   Z  \0  \0  \v  \0  \0  \0   A   L
            G   E   R   I   A
          ...              \n  \0  \0  \0   Z   A   M   B   I   A 024  \0
           \0  \0 001  \0  \0  \0 002  \0  \0  \0 006  \0  \0  \0   Z   W
           \0  \0  \f  \0  \0  \0   Z   I   M   B   A   B   W   E

COMPATIBILITY
   SQL92
       There is no COPY statement in SQL92.

SQL - Language Statements 15 August 1999                        1