Provided by: postgresql-client-8.4_8.4.11-1_i386 bug


       COPY - copy data between a file and a table


       COPY tablename [ ( column [, ...] ) ]
           FROM { 'filename' | STDIN }
           [ [ WITH ]
                 [ BINARY ]
                 [ OIDS ]
                 [ DELIMITER [ AS ] 'delimiter' ]
                 [ NULL [ AS ] 'null string' ]
                 [ CSV [ HEADER ]
                       [ QUOTE [ AS ] 'quote' ]
                       [ ESCAPE [ AS ] 'escape' ]
                       [ FORCE NOT NULL column [, ...] ]

       COPY { tablename [ ( column [, ...] ) ] | ( query ) }
           TO { 'filename' | STDOUT }
           [ [ WITH ]
                 [ BINARY ]
                 [ OIDS ]
                 [ DELIMITER [ AS ] 'delimiter' ]
                 [ NULL [ AS ] 'null string' ]
                 [ CSV [ HEADER ]
                       [ QUOTE [ AS ] 'quote' ]
                       [ ESCAPE [ AS ] 'escape' ]
                       [ FORCE QUOTE column [, ...] ]


       COPY  moves  data  between  PostgreSQL  tables and standard file-system
       files. COPY TO copies the 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 TO can also copy the results of a SELECT

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

       COPY with a file name instructs the PostgreSQL server to directly  read
       from  or write to a file. The file 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.


              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 of the table will be copied.

       query  A SELECT [select(7)] or VALUES [values(7)] command whose results
              are to be copied.  Note that parentheses are required around the

              The  absolute  path  name  of  the input or output file. Windows
              users might need to use an E''  string  and  double  backslashes
              used as path separators.

       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 in binary format rather
              than as text. You cannot specify the  DELIMITER,  NULL,  or  CSV
              options in binary mode.

       OIDS   Specifies  copying  the OID for each row. (An error is raised if
              OIDS is specified for a table that does not have OIDs, or in the
              case of copying a query.)

              The  single  ASCII  character that separates columns within each
              row (line) of the file. The default is a tab character  in  text
              mode, a comma in CSV mode.

       null string
              The  string  that  represents  a  null  value. The default is \N
              (backslash-N) in text mode, and an unquoted empty string in  CSV
              mode.  You  might  prefer  an empty string even in text mode for
              cases where you don't  want  to  distinguish  nulls  from  empty

              Note:  When  using  COPY  FROM,  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 with COPY TO.

       CSV    Selects Comma Separated Value (CSV) mode.

       HEADER Specifies that the file contains a header line with the names of
              each column in the file. On output, the first line contains  the
              column  names  from  the  table, and on input, the first line is

       quote  Specifies the  ASCII  quotation  character  in  CSV  mode.   The
              default is double-quote.

       escape Specifies  the ASCII character that should appear before a QUOTE
              data character value in CSV mode.   The  default  is  the  QUOTE
              value (usually double-quote).

              In  CSV COPY TO mode, forces quoting to be used for all non-NULL
              values in each specified column.  NULL output is never quoted.

              In CSV COPY FROM mode, process each specified column  as  though
              it  were quoted and hence not a NULL value. For the default null
              string in CSV mode (''), this causes missing values to be  input
              as zero-length strings.


       On  successful  completion, a COPY command returns a command tag of the

       COPY count

       The count is the number of rows copied.


       COPY can only be used with plain tables, not with views.  However,  you
       can write COPY (SELECT * FROM viewname) TO ....

       The  BINARY key word causes all data to be stored/read as binary format
       rather than as text. It is somewhat faster than the normal  text  mode,
       but  a binary-format file is less portable across machine architectures
       and PostgreSQL versions.  Also, the binary format  is  very  data  type
       specific;  for  example  it  will not work to output binary data from a
       smallint column and read it into an integer column,  even  though  that
       would work fine in text format.

       You  must  have  select privilege on the table whose values are read by
       COPY TO, and insert privilege  on  the  table  into  which  values  are
       inserted  by  COPY  FROM. It is sufficient to have column privileges on
       the column(s) listed in the command.

       Files named in a COPY command are  read  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 is
       only allowed to database superusers, since it allows reading or writing
       any file 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 a
       file accessible to the psql client. Thus, file accessibility and access
       rights depend on the client rather than the server when \copy is used.

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

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

       COPY  input  and output is affected by DateStyle. To ensure portability
       to other PostgreSQL installations that might use non-default  DateStyle
       settings,  DateStyle  should  be set to ISO before using COPY TO. It is
       also a good idea to  avoid  dumping  data  with  IntervalStyle  set  to
       sql_standard,  because negative interval values might be misinterpreted
       by a server that has a different setting for IntervalStyle.

       Input data is interpreted according to the current client encoding, and
       output  data is encoded in the the current client encoding, even if the
       data does not pass through the client but is read from or written to  a

       COPY  stops  operation  at  the  first  error.  This should not lead to
       problems in the event of a COPY TO, but the target table  will  already
       have  received  earlier  rows  in  a  COPY FROM. These rows will not be
       visible or accessible, but they still occupy  disk  space.  This  might
       amount  to  a  considerable  amount of wasted disk space if the failure
       happened well into a large copy operation. You  might  wish  to  invoke
       VACUUM to recover the wasted space.


       When  COPY  is used without the BINARY or CSV options, the data read or
       written is a text file with one line per table row.  Columns in  a  row
       are separated 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 is used in place of columns that are null.  COPY FROM will raise
       an  error  if any line of the input file contains more or fewer columns
       than are expected.  If OIDS is specified, the OID is read or written as
       the first column, preceding the user data columns.

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

       Backslash  characters  (\)  can  be used in 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 they appear as part of a column value:  backslash  itself,  newline,
       carriage return, and the current delimiter character.

       The  specified  null  string  is  sent  by  COPY  TO without adding any
       backslashes; conversely, COPY FROM matches the input against  the  null
       string before removing backslashes. Therefore, a null string such as \N
       cannot be confused with the  actual  data  value  \N  (which  would  be
       represented 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
       specifies  the  character  with  that  numeric  code\xdigitsBackslash x
       followed by one or two hex digits specifies  the  character  with  that
       numeric  code Presently, COPY TO will never emit an octal or hex-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 the above
       table will be taken to represent  itself.  However,  beware  of  adding
       backslashes  unnecessarily,  since  that  might  accidentally produce a
       string matching the end-of-data marker (\.) or the null string  (\N  by
       default).  These  strings will be recognized before any other backslash
       processing is done.

       It is strongly  recommended  that  applications  generating  COPY  data
       convert  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
       newline by a backslash and  newline.   However,  these  representations
       might  not  be  accepted  in  future  releases.   They  are also highly
       vulnerable to  corruption  if  the  COPY  file  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  Microsoft   Windows   instead   output   carriage
       return/newline  (``\r\n''),  but  only  for  COPY to a server file; 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 due to un-backslashed newlines or carriage returns that
       were meant as data, COPY FROM will complain if the line endings in  the
       input are not all alike.

       This  format  is  used  for importing and exporting the Comma Separated
       Value  (CSV)  file  format  used  by  many  other  programs,  such   as
       spreadsheets.  Instead  of  the  escaping used by PostgreSQL's standard
       text  mode,  it  produces  and  recognizes  the  common  CSV   escaping

       The  values in each record are separated by the DELIMITER character. If
       the value contains the delimiter character, the  QUOTE  character,  the
       NULL  string, a carriage return, or line feed character, then the whole
       value is  prefixed  and  suffixed  by  the  QUOTE  character,  and  any
       occurrence  within  the  value  of  a  QUOTE  character  or  the ESCAPE
       character is preceded by the escape character.  You can also use  FORCE
       QUOTE  to  force  quotes  when  outputting  non-NULL values in specific

       The CSV format has no standard way to distinguish a NULL value from  an
       empty  string.   PostgreSQL's  COPY handles this by quoting.  A NULL is
       output as the NULL parameter string and is not quoted, while a non-NULL
       value  matching  the NULL parameter string is quoted. For example, with
       the default settings, a NULL is written as an  unquoted  empty  string,
       while  an  empty  string data value is written with double quotes ("").
       Reading values follows similar rules. You can use  FORCE  NOT  NULL  to
       prevent NULL input comparisons for specific columns.

       Because backslash is not a special character in the CSV format, \., the
       end-of-data marker, could also appear as a data  value.  To  avoid  any
       misinterpretation, a \.  data value appearing as a lone entry on a line
       is automatically quoted on output, and on  input,  if  quoted,  is  not
       interpreted  as  the  end-of-data  marker.  If  you  are loading a file
       created by another application that has a single  unquoted  column  and
       might  have  a  value  of \., you might need to quote that value in the
       input file.

              Note: In CSV mode, all  characters  are  significant.  A  quoted
              value  surrounded  by  white space, or any characters other than
              DELIMITER, will include those characters. This can cause  errors
              if  you import data from a system that pads CSV lines with white
              space out to some fixed width. If such a  situation  arises  you
              might  need  to  preprocess  the CSV file to remove the trailing
              white space, before importing the data into PostgreSQL.

              Note: CSV mode will both recognize and produce  CSV  files  with
              quoted  values  containing  embedded  carriage  returns and line
              feeds. Thus the files are not strictly one line  per  table  row
              like text-mode files.

              Note:  Many  programs  produce strange and occasionally perverse
              CSV files, so the file  format  is  more  a  convention  than  a
              standard.  Thus  you  might  encounter some files that cannot be
              imported using this mechanism, and COPY might produce files that
              other programs cannot process.

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

       The file header consists of 15 bytes of fixed  fields,  followed  by  a
       variable-length header extension area. The fixed fields are:

              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
              format.  Bits  are  numbered from 0 (LSB) to 31 (MSB). Note that
              this field is stored in network  byte  order  (most  significant
              byte  first),  as  are  all  the integer fields used in the file
              format. 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 data; if 0, not

       Header extension area length
              32-bit  integer,  length  in  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 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).

       Each tuple begins with a 16-bit integer 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 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 the NULL case.

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

       Presently, all data values in a COPY BINARY file are assumed to  be  in
       binary  format  (format  code  one).  It  is  anticipated that a future
       extension might 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 particular the  *send  and
       *recv  functions for each column's data type (typically these functions
       are  found  in  the  src/backend/utils/adt/  directory  of  the  source

       If OIDs are included in the file, the OID field immediately follows the
       field-count word. It is a normal field except that it's not included in
       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 that ever proves desirable.

       The  file 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 if a field-count word is neither -1 nor
       the  expected  number  of columns. This provides an extra check against
       somehow getting out of sync with the data.


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


       To copy data from a file into the country table:

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

       To copy into a file just the countries whose names start with 'A':

       COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

       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 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  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


       There is no COPY statement in the SQL standard.

       The  following  syntax  was  used  before PostgreSQL version 7.3 and is
       still supported:

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

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