COPY - Copies data between files and tables


SYNOPSIS

       COPY [ BINARY ] table [ WITH OIDS ]
           FROM { 'filename' | stdin }
           [ [USING] DELIMITERS 'delimiter' ]
           [ WITH NULL AS 'null string' ]
       COPY [ BINARY ] table [ WITH OIDS ]
           TO { 'filename' | stdout }
           [ [USING] DELIMITERS 'delimiter' ]
           [ WITH NULL AS 'null string' ]

   INPUTS
       BINARY Changes  the  behavior of field formatting, forcing
              all data to be stored  or  read  in  binary  format
              rather  than as text.  The DELIMITERS and WITH NULL
              options are irrelevant for binary format.

       table  The name of an existing table.

       WITH OIDS
              Specifies copying 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 the client applica­
              tion.

       stdout Specifies  that  output goes to the client applica­
              tion.

       delimiter
              The character that separates fields within each row
              (line) of the file.

       null string
              The  string  that  represents  a  NULL  value.  The
              default is ``\N'' (backslash-N).  You might  prefer
              an empty string, for example.

              Note: On a copy in, any data item that matches this
              string will be stored  as  a  NULL  value,  so  you
              should  make  sure  that you use the same string as
              you used on copy out.

       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 TO copies the  entire  contents  of  a
       table  to  a file, while COPY FROM copies data from a file
       to a table (appending the data to whatever is in the table
       already).

       COPY  instructs the Postgres backend to directly read from
       or write to a file. If a file name is specified, the  file
       must  be  accessible  to  the backend and the name must be
       specified from the viewpoint of the backend.  If stdin  or
       stdout  is  specified, data flows through the client fron­
       tend to the backend.

              Tip: Do not confuse COPY with the psql  instruction
              \copy.   \copy  invokes  COPY FROM stdin or COPY TO
              stdout, and then fetches/stores the data in a  file
              accessible to the psql client.  Thus, file accessi­
              bility and  access  rights  depend  on  the  client
              rather than the backend when \copy is used.

   NOTES
       The  BINARY  keyword will force all data to be stored/read
       as binary format rather  than  as  text.  It  is  somewhat
       faster  than  the  normal  copy command, but a binary copy
       file is not portable across machine architectures.

       By default, a text copy uses a tab ("\t") character  as  a
       delimiter  between  fields.  The  field  delimiter  may 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  backslash
       quoted.   Note that the delimiter is always a single char­
       acter.  If multiple characters are specified in the delim­
       iter string, only the first character is used.

       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.

       COPY TO neither invokes rules nor acts on column defaults.
       It does invoke triggers and check constraints.

       lead to problems in the event of a COPY FROM, but the tar­
       get relation will already have received earlier rows in  a
       COPY TO. These rows will not be visible or accessible, but
       they still occupy disk space. This may amount to a consid­
       erable amount of wasted disk space if the failure happened
       well into a large copy operation. You may wish  to  invoke
       VACUUM to recover the wasted space.

       Files named in a COPY command are read or written directly
       by the backend, not by the client application.  Therefore,
       they  must  reside  on  or  be  accessible to the database
       server machine, not the client. They must be accessible to
       and  readable or writable by the Postgres user (the userid
       the backend runs as), not the client.  COPY naming a  file
       is  only  allowed  to database superusers, since it allows
       writing on any file that the  backend  has  privileges  to
       write on.

              Tip:  The  psql  instruction  \copy reads or writes
              files on the client machine with the client's  per­
              missions, so it is not restricted to superusers.

       It is recommended that the filename used in COPY always be
       specified as an absolute path. This  is  enforced  by  the
       backend  in  the case of COPY TO, but for COPY FROM you do
       have the option of reading from a file specified by a rel­
       ative  path.  The path will be interpreted relative to the
       backend's working directory (somewhere below $PGDATA), not
       the client's working directory.


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>

       Note  that  the  end of each row is marked by a Unix-style
       newline ("\n"). COPY FROM will not behave  as  desired  if
       given a file containing DOS- or Mac-style newlines.

       The  OID  is  emitted  as the first column if WITH OIDS is


       If COPY TO  is  sending  its  output  to  standard  output
       instead of a file, after the last row it will send a back­
       slash ("\") and a period  (".")  followed  by  a  newline.
       Similarly, if COPY FROM 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 FROM will terminate cor­
       rectly  (followed by the backend itself) if the input con­
       nection is closed before this special end-of-file  pattern
       is found.

       The backslash character has other special meanings. A lit­
       eral backslash character is represented as two consecutive
       backslashes ("\\"). A literal tab character is represented
       as a backslash and a tab.  (If  you  are  using  something
       other  than  tab  as  the column delimiter, backslash that
       delimiter character to include it in data.) A literal new­
       line  character  is  represented as a backslash and a new­
       line. 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
       The  file  format used for COPY BINARY changed in Postgres
       v7.1. The new format consists of a file  header,  zero  or
       more tuples, and a file trailer.

   FILE HEADER
       The file header consists of 24 bytes of fixed fields, fol­
       lowed by a  variable-length  header  extension  area.  The
       fixed fields are:

       Signature
              12-byte  sequence  "PGBCOPY\n\377\r\n\0"  ---  note
              that the null is a required part of the  signature.
              (The  signature is designed to allow easy identifi­
              cation  of  files  that  have  been  munged  by   a
              non-8-bit-clean  transfer.  This  signature will be
              changed  by  newline-translation  filters,  dropped
              nulls, dropped high bits, or parity changes.)

       Integer layout field
              int32  constant  0x01020304 in source's byte order.
              Potentially, a reader could engage in byte-flipping
              of  subsequent  fields  if  the wrong byte order is
              detected here.

       Flags field
              int32 bit mask to denote important aspects  of  the
              file  format.  Bits are numbered from 0 (LSB) to 31
              (MSB) --- note  that  this  field  is  stored  with

              fields. Bits 16-31 are reserved to denote  critical
              file  format  issues;  a  reader should abort if it
              finds an unexpected bit set in  this  range.   Bits
              0-15  are  reserved  to signal backwards-compatible
              format issues; a reader should  simply  ignore  any
              unexpected  bits set in this range.  Currently only
              one flag bit is defined, and the rest must be zero:

              Bit 16 if  1,  OIDs are included in the dump; if 0,
                     not

       Header extension area length
              int32 length in bytes of remainder of  header,  not
              including self. In the initial version this will be
              zero, and  the  first  tuple  follows  immediately.
              Future changes to the format might allow additional
              data to be present in the header. A  reader  should
              silently  skip  over  any  header extension data it
              does not know what to do with.

       The header extension  area  is  envisioned  to  contain  a
       sequence  of  self-identifying  chunks. The flags field is
       not intended to tell readers  what  is  in  the  extension
       area. Specific design of header extension contents is left
       for a later release.

       This design allows for  both  backwards-compatible  header
       additions  (add  header extension chunks, or set low-order
       flag bits) and non-backwards-compatible changes (set high-
       order flag bits to signal such changes, and add supporting
       data to the extension area if needed).

   TUPLES
       Each tuple begins with an int16 count  of  the  number  of
       fields  in  the  tuple.  (Presently, all tuples in a table
       will have the same count, but that  might  not  always  be
       true.)  Then,  repeated for each field in the tuple, there
       is an int16 typlen word possibly followed by  field  data.
       The typlen field is interpreted thus:

       Zero   Field is NULL. No data follows.

       > 0    Field  is  a fixed-length datatype. Exactly N bytes
              of data follow the typlen word.

       -1     Field is a varlena datatype. The  next  four  bytes
              are  the  varlena  header, which contains the total
              value length including itself.

       < -1   Reserved for future use.

       matches  the  expected  typlen for the destination column.
       This provides a simple but very useful check that the data
       is as expected.

       There  is  no  alignment  padding  or any other extra data
       between fields.  Note also that the format does  not  dis­
       tinguish  whether a datatype is pass-by-reference or pass-
       by-value. Both of these provisions  are  deliberate:  they
       might  help  improve portability of the files (although of
       course endianness  and  floating-point-format  issues  can
       still keep you from moving a binary file across machines).

       If OIDs are included in the dump, the  OID  field  immedi­
       ately  follows  the field-count word. It is a normal field
       except that it's not included in the field-count. In  par­
       ticular  it  has  a typlen --- this will allow handling of
       4-byte vs 8-byte OIDs without  too  much  pain,  and  will
       allow OIDs to be shown as NULL if we someday allow OIDs to
       be optional.

   FILE TRAILER
       The file trailer consists of an int16 word containing  -1.
       This  is  easily  distinguished from a tuple's field-count
       word.

       A reader should report an error if a field-count  word  is
       neither  -1  nor the expected number of columns. This pro­
       vides an extra check against somehow getting out  of  sync
       with the data.


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


       Note that the white space on each line is actually a  TAB.

       The following is 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), the second is text, and  the
       third  is  integer.  All the rows have a null value in the
       third field.

       0000000   P   G   B   C   O   P   Y  \n 377  \r  \n  \0 004 003 002 001
       0000020  \0  \0  \0  \0  \0  \0  \0  \0 003  \0 377 377 006  \0  \0  \0
       0000040   A   F 377 377 017  \0  \0  \0   A   F   G   H   A   N   I   S
       0000060   T   A   N  \0  \0 003  \0 377 377 006  \0  \0  \0   A   L 377
       0000100 377  \v  \0  \0  \0   A   L   B   A   N   I   A  \0  \0 003  \0
       0000120 377 377 006  \0  \0  \0   D   Z 377 377  \v  \0  \0  \0   A   L
       0000140   G   E   R   I   A  \0  \0 003  \0 377 377 006  \0  \0  \0   Z
       0000160   M 377 377  \n  \0  \0  \0   Z   A   M   B   I   A  \0  \0 003
       0000200  \0 377 377 006  \0  \0  \0   Z   W 377 377  \f  \0  \0  \0   Z
       0000220   I   M   B   A   B   W   E  \0  \0 377 377


COMPATIBILITY

   SQL92
       There is no COPY statement in SQL92.


Man(1) output converted with man2html