copy man page on aLinux

Man page or keyword search:  
man Server   7435 pages
apropos Keyword Search (all sections)
Output format
aLinux logo
[printable version]

COPY()				 SQL Commands				COPY()

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

       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.

	      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.

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

	      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

	      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

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

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

	      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.

       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.

       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.

       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.

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

       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

       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‐

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

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

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

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

List of man pages available for aLinux

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
Vote for polarhome
Free Shell Accounts :: the biggest list on the net