COPY(7) 							   SQL Commands 							   COPY(7)

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 [, ...] ) ] | ( 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 [, ...] ]

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). COPY TO can also copy the results of a  SELECT
       query.

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

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

       delimiter
	      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 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  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 ignored.

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

       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.

OUTPUTS

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

       COPY count

       The count is the number of rows copied.

NOTES

       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  rela-
       tive 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 Dat-
       eStyle 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 file.

       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.

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 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,  new-
       line, 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: SequenceRepresentsBackspace (ASCII 8)fForm feed (ASCII 12)
New-
       line (ASCII 10)
Carriage return (ASCII 13)	Tab (ASCII 9)vVertical tab (ASCII 11)digitsBackslash followed by one to three  octal  digits
       specifies  the  character  with	that  numeric  codexdigitsBackslash 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 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 convert data newlines and carriage returns to the 
 and 
 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 vulner-
       able 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  (``
'').  Servers	running  on  Microsoft	Windows  instead  output  carriage
       return/newline (``
''), but only for COPY to a server file; for consistency across platforms, COPY TO STDOUT always sends ``
'' regard-
       less 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.

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

   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
377
  -- 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-back-
       wards-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
       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 dis-
       tribution).

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

       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	
 377	
  
	  	  	  
       0000020	  	   003  	   002   A	 F  	   013   A
       0000040	 F   G	 H   A	 N   I	 S   T	 A   N 377 377 377 377	 003
       0000060	  	 002	 A   L	  	 007	 A   L	 B   A	 N   I
       0000100	 A 377 377 377 377   003  	   002   D	 Z  	  
       0000120 007   A	 L   G	 E   R	 I   A 377 377 377 377	 003	  
       0000140	 002	 Z   M	  	 006	 Z   A	 M   B	 I   A 377 377
       0000160 377 377	 003	  	 002	 Z   W	  	  	 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					    2010-05-14								   COPY(7)