Seth Woolley's Man Viewer

copy(7) - COPY - copy data between a file and a table - man 7 copy

([section] manual, -k keyword, -K [section] search, -f whatis)
man plain no title

COPY(7)                          SQL Commands                          COPY(7)



NAME
       COPY - copy data between a file(1,n) and a table


SYNOPSIS
       COPY tablename [ ( column [, ...] ) ]
           FROM { 'filename' | STDIN }
           [ [ WITH ]
                 [ BINARY ]
                 [ OIDS ]
                 [ DELIMITER [ AS ] 'delimiter' ]
                 [ NULL [ AS ] 'null string(3,n)' ] ]

       COPY tablename [ ( column [, ...] ) ]
           TO { 'filename' | STDOUT }
           [ [ WITH ]
                 [ BINARY ]
                 [ OIDS ]
                 [ DELIMITER [ AS ] 'delimiter' ]
                 [ NULL [ AS ] 'null string(3,n)' ] ]


DESCRIPTION
       COPY  moves  data  between  PostgreSQL  tables and standard file-system
       files. COPY TO copies the contents of a table to  a  file(1,n),  while  COPY
       FROM copies data from a file(1,n) to a table (appending the data to whatever
       is in(1,8) the table already).

       If a list of columns is specified, COPY will only copy the data in(1,8)  the
       specified columns to or from the file.  If there are any columns in(1,8) the
       table that are not in(1,8) the  column  list,  COPY  FROM  will  insert  the
       default values for those columns.

       COPY  with a file(1,n) name instructs the PostgreSQL server to directly read(2,n,1 builtins)
       from or write(1,2) to a file. The file(1,n) must be accessible to the server  and
       the name must be specified from the viewpoint of the server. When STDIN
       or STDOUT is specified, data is transmitted via the connection  between
       the client and the server.

PARAMETERS
       tablename
              The name (optionally schema-qualified) of an existing table.

       column An  optional  list of columns to be copied. If no column list is
              specified, all columns will be used.

       filename
              The absolute path name of the input or output file.

       STDIN  Specifies that input comes from the client application.

       STDOUT Specifies that output goes to the client application.

       BINARY Causes all data to be stored or read(2,n,1 builtins)  in(1,8)  binary  format  rather
              than  as  text. You cannot specify the DELIMITER or NULL options
              in(1,8) binary mode.

       OIDS   Specifies copying the OID for each row. (An error(8,n) is  raised  if(3,n)
              OIDS is specified for a table that does not have OIDs.)

       delimiter
              The  single  character  that  separates  columns within each row
              (line) of the file. The default is a tab character.

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

              Note:  On  a  COPY  FROM, any data item that matches this string(3,n)
              will be stored as a null value, so you should make sure that you
              use the same string(3,n) as you used with COPY TO.


NOTES
       COPY can only be used with plain tables, not with views.

       The  BINARY key word causes all data to be stored/read(2,n,1 builtins) as binary format
       rather than as text. It is somewhat faster than the normal  text  mode,
       but  a binary-format file(1,n) is less(1,3) portable across machine architectures
       and PostgreSQL versions.

       You must have select(2,7,2 select_tut) privilege on the table whose values  are  read(2,n,1 builtins)  by
       COPY  TO,  and  insert  privilege  on  the  table into which values are
       inserted by COPY FROM.

       Files named(5,8) in(1,8) a COPY command are  read(2,n,1 builtins)  or  written  directly  by  the
       server,  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 PostgreSQL user
       (the user ID the server runs as), not the client. COPY naming a file(1,n) is
       only allowed to database superusers, since it allows reading or writing
       any file(1,n) that the server has privileges to access.

       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(1,8) a
       file(1,n) accessible to the psql client. Thus, file(1,n) accessibility and access(2,5)
       rights  depend on the client rather than the server when \copy is used.

       It is recommended that the file(1,n) name used in(1,8) COPY always  be  specified
       as an absolute path. This is enforced by the server in(1,8) the case of COPY
       TO, but for COPY FROM you do have the option of  reading  from  a  file(1,n)
       specified  by a relative path. The path will be interpreted relative to
       the working directory of the server process (somewhere below  the  data
       directory), not the client's working directory.

       COPY  FROM will invoke any triggers and check constraints on the desti-
       nation table. However, it will not invoke rules.

       COPY stops operation at the first error. This should not lead to  prob-
       lems  in(1,8) the event of a COPY TO, but the target table will already have
       received earlier rows in(1,8) a COPY FROM. These rows will not be visible or
       accessible, but they still occupy disk space. This may amount to a con-
       siderable amount of wasted disk space if(3,n) the failure happened well into
       a  large  copy  operation. You may wish to invoke VACUUM to recover the
       wasted space.

FILE FORMATS
   TEXT FORMAT
       When COPY is used without the BINARY option, the data read(2,n,1 builtins)  or  written
       is a text file(1,n) with one line per table row.  Columns in(1,8) a row are sepa-
       rated by the delimiter character.  The  column  values  themselves  are
       strings  generated  by  the output function, or acceptable to the input
       function, of each attribute's data type. The specified null  string(3,n)  is
       used  in(1,8) place of columns that are null.  COPY FROM will raise(3,n) an error(8,n)
       if(3,n) any line of the input file(1,n) contains more or fewer columns  than  are
       expected.   If  OIDS  is  specified,  the OID is read(2,n,1 builtins) or written as the
       first column, preceding the user data columns.

       End of data can be represented by a single line containing  just  back-
       slash-period  (\.). An end-of-data marker is not necessary when reading
       from a file(1,n), since the end of file(1,n) serves perfectly well; it is  needed
       only  when  copying  data  to or from client applications using pre-3.0
       client protocol.

       Backslash characters (\) may be used in(1,8) the COPY  data  to  quote  data
       characters  that  might otherwise be taken as row or column delimiters.
       In particular, the following characters must be preceded by a backslash
       if(3,n)  they  appear  as part of a column value: backslash itself, newline,
       carriage return, and the current delimiter character.

       The specified null string(3,n) is sent by COPY TO without adding  any  back-
       slashes;  conversely,  COPY  FROM  matches  the  input against the null
       string(3,n) before removing backslashes. Therefore, a null string(3,n) such as \N
       cannot be confused with the actual data value \N (which would be repre-
       sented as \\N).

       The following special backslash sequences are recognized by COPY  FROM:
       SequenceRepresents\bBackspace  (ASCII 8)\fForm feed (ASCII 12)\nNewline
       (ASCII 10)\rCarriage return (ASCII  13)\tTab  (ASCII  9)\vVertical  tab
       (ASCII 11)\digitsBackslash followed by one to three octal digits speci-
       fies the character with that numeric code Presently, COPY TO will never
       emit  an  octal-digits  backslash  sequence,  but it does use the other
       sequences listed above for those control characters.

       Any other backslashed character that is not mentioned in(1,8) the above  ta-
       ble  will be taken to represent itself. However, beware of adding back-
       slashes unnecessarily, since that might accidentally produce  a  string(3,n)
       matching  the  end-of-data  marker  (\.)  or  the  null  string(3,n)  (\N by
       default). These strings will be recognized before any  other  backslash
       processing is done.

       It  is strongly recommended that applications generating COPY data con-
       vert data newlines and carriage returns to  the  \n  and  \r  sequences
       respectively.  At  present  it is possible to represent a data carriage
       return by a backslash and carriage return, and to represent a data new-
       line  by a backslash and newline.  However, these representations might
       not be accepted in(1,8) future releases.  They are also highly vulnerable to
       corruption  if(3,n)  the  COPY file(1,n) is transferred across different machines
       (for example, from Unix to Windows or vice versa).

       COPY TO will terminate each row with  a  Unix-style  newline  (``\n'').
       Servers  running  on  MS Windows instead output carriage return/newline
       (``\r\n''), but only for COPY to a server file(1,n); for consistency  across
       platforms,  COPY  TO  STDOUT  always  sends ``\n'' regardless of server
       platform.  COPY FROM can handle lines ending  with  newlines,  carriage
       returns,  or  carriage return/newlines. To reduce the risk of error(8,n) due
       to un-backslashed newlines or carriage returns that were meant as data,
       COPY  FROM  will  complain if(3,n) the line endings in(1,8) the input are not all
       alike.

   BINARY FORMAT
       The file(1,n) format used for COPY BINARY changed in(1,8) PostgreSQL 7.4. The new
       format  consists  of  a file(1,n) header, zero or more tuples containing the
       row data, and a file(1,n) trailer. Headers and data are now in(1,8) network  byte
       order.

   FILE HEADER
       The  file(1,n)  header  consists  of 15 bytes of fixed fields, followed by a
       variable-length header extension area. The fixed fields are:

       Signature
              11-byte sequence PGCOPY\n\377\r\n\0 --- note that the zero  byte
              is  a required part of the signature. (The signature is designed
              to allow easy identification of files that have been munged by a
              non-8-bit-clean transfer. This signature will be changed by end-
              of-line-translation filters, dropped zero  bytes,  dropped  high
              bits, or parity changes.)

       Flags field
              32-bit  integer bit mask to denote important aspects of the file(1,n)
              format. Bits are numbered from 0 (LSB) to 31  (MSB).  Note  that
              this  field  is  stored  in(1,8) network byte order (most significant
              byte first), as are all the integer fields used in(1,8) the file(1,n) for-
              mat.  Bits  16-31  are  reserved  to denote critical file(1,n) format
              issues; a reader should abort(3,7) if(3,n) it finds an unexpected bit  set(7,n,1 builtins)
              in(1,8)  this  range. Bits 0-15 are reserved to signal(2,7) backwards-com-
              patible format issues; a reader should simply ignore  any  unex-
              pected  bits  set(7,n,1 builtins)  in(1,8) this range. Currently only one flag bit is
              defined, and the rest must be zero:

              Bit 16 if(3,n) 1, OIDs are included in(1,8) the data; if(3,n) 0, not


       Header extension area length
              32-bit integer, length in(1,8) bytes  of  remainder  of  header,  not
              including  self.   Currently,  this is zero, and the first tuple
              follows immediately. Future changes to the  format  might  allow
              additional  data  to  be  present in(1,8) 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(1,8) the extension area. Specific design of header extension con-
       tents is left for a later release.

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

   TUPLES
       Each  tuple  begins with a 16-bit integer count of the number of fields
       in(1,8) the tuple. (Presently, all tuples in(1,8) a  table  will  have  the  same
       count,  but  that  might  not  always be true.) Then, repeated for each
       field in(1,8) the tuple, there is a 32-bit length word followed by that many
       bytes  of field data. (The length word does not include itself, and can
       be zero.) As a special case, -1 indicates a NULL field value. No  value
       bytes follow in(1,8) the NULL case.

       There is no alignment padding or any other extra data between fields.

       Presently,  all  data values in(1,8) a COPY BINARY file(1,n) are assumed to be in(1,8)
       binary format (format code one). It is anticipated that a future exten-
       sion  may  add a header field that allows per-column format codes to be
       specified.

       To determine the appropriate binary format for the  actual  tuple  data
       you  should  consult the PostgreSQL source, in(1,8) particular the *send(2,n) and
       *recv functions for each column's data type (typically these  functions
       are found in(1,8) the src/backend/utils/adt/ directory of the source distri-
       bution).

       If OIDs are included in(1,8) the file(1,n), the OID field immediately follows the
       field-count word. It is a normal field except that it's not included in(1,8)
       the field-count. In particular it has a length word --- 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(3,n) that ever proves desirable.

   FILE TRAILER
       The file(1,n) trailer consists of a 16-bit integer word containing -1.  This
       is easily distinguished from a tuple's field-count word.

       A reader should report an error(8,n) if(3,n) a field-count word is neither -1 nor
       the expected number of columns. This provides an  extra  check  against
       somehow getting out of sync(1,2,8) with the data.

EXAMPLES
       The  following  example copies a table to the client using the vertical
       bar (|) as the field delimiter:

       COPY country TO STDOUT WITH DELIMITER '|';


       To copy data from a file(1,n) into the country table:

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


       Here is a sample of data suitable for copying into a table from STDIN:

       AF      AFGHANISTAN
       AL      ALBANIA
       DZ      ALGERIA
       ZM      ZAMBIA
       ZW      ZIMBABWE

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

       The following is the same data, output in(1,8) binary format.  The  data  is
       shown  after  filtering  through  the Unix utility od -c. The table has
       three columns; the first has type char(2), the second  has  type  text,
       and  the  third has type integer. All the rows have a null value in(1,8) the
       third column.

       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 006   Z   A   M   B   I   A 377 377
       0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
       0000200   M   B   A   B   W   E 377 377 377 377 377 377


COMPATIBILITY
       There is no COPY statement in(1,8) the SQL standard.

       The following syntax was used before  PostgreSQL  version(1,3,5)  7.3  and  is
       still supported:

       COPY [ BINARY ] tablename [ WITH OIDS ]
           FROM { 'filename' | STDIN }
           [ [USING] DELIMITERS 'delimiter' ]
           [ WITH NULL AS 'null string(3,n)' ]

       COPY [ BINARY ] tablename [ WITH OIDS ]
           TO { 'filename' | STDOUT }
           [ [USING] DELIMITERS 'delimiter' ]
           [ WITH NULL AS 'null string(3,n)' ]




SQL - Language Statements         2003-11-02                           COPY(7)

References for this manual (incoming links)