DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

/usr/man2/cat.l/copy.l.Z(/usr/man2/cat.l/copy.l.Z)





NAME

       COPY - copy data between a file and a table


SYNOPSIS

       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 [, ...] ) ]
           TO { 'filename' | STDOUT }
           [ [ WITH ]
                 [ BINARY ]
                 [ HEADER ]
                 [ OIDS ]
                 [ DELIMITER [ AS ] 'delimiter' ]
                 [ NULL [ AS ] 'null string' ]
                 [ CSV [ HEADER ]
                       [ QUOTE [ AS ] 'quote' ]
                       [ ESCAPE [ AS ] 'escape' ]
                       [ FORCE QUOTE column [, ...] ]


DESCRIPTION

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

       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.


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

       delimiter
              The  single  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 a empty value with no quotes  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
              strings.

              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 the file contains a header line with the names of each
              column in the file. On output, the first line contains the  col-
              umn  names  from  the  table,  and  on  input, the first line is
              ignored.

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

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

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

       FORCE NOT NULL
              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.


NOTES

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

       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.

       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.

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

       COPY stops operation at the first error. This should not lead to  prob-
       lems  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 may amount to a con-
       siderable 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.


FILE FORMATS

   TEXT FORMAT
       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  back-
       slash-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 (\) may 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  back-
       slashes;  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 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\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  ta-
       ble  will be taken to represent itself. However, beware of adding back-
       slashes 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 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 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 con-
       sistency  across  platforms, COPY TO STDOUT always sends ``\n'' regard-
       less of server platform.  COPY FROM can handle lines ending  with  new-
       lines,  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.

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

       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  occur-
       rence  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 columns.

       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 string and is not quoted, while a data value match-
       ing the NULL string is quoted. Therefore, using the default settings, a
       NULL  is  written as an unquoted empty string, while an empty string is
       written with double quotes (""). Reading values follows similar  rules.
       You  can  use FORCE NOT NULL to prevent NULL input comparisons for spe-
       cific columns.

              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  stan-
              dard.  Thus  you  might  encounter  some  files  that  cannot be
              imported using this mechanism, and COPY might produce files that
              other programs cannot process.

   BINARY FORMAT
       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
       order.

   FILE HEADER
       The file 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
              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 for-
              mat. 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-com-
              patible  format  issues; a reader should simply ignore any unex-
              pected 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 con-
       tents 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 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 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 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 distri-
       bution).

       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.

   FILE TRAILER
       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.


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 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' using
       a temporary table which is automatically deleted:

       BEGIN;
       CREATE TEMP TABLE a_list_countries AS
           SELECT * FROM country WHERE country_name LIKE 'A%';
       COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy';
       ROLLBACK;

       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


COMPATIBILITY

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

SQL - Language Statements         2005-11-05                            COPY()

Man(1) output converted with man2html