COPY

Name

COPY — Copies data between files and tables.
   COPY [BINARY] table [WITH OIDS]
         TO|FROM 'filename'|stdin|stdout
         [USING DELIMITERS 'delimiter']
  

Inputs

table

The name of a table.

delimiter

A character that delimits fields.

Outputs

Status

COPY

The copy completed successfully.

ERROR: error message

The copy failed for the reason stated in the error message.

Description

COPY moves data between PostgreSQL tables and standard Unix files. The keyword BINARY changes the behavior of field formatting, as described below. Table is the name of an existing table. The keyword WITH OIDS copies the internal unique object id (OID) for each row. Filename is the absolute Unix pathname of the file. In place of a filename, the keywords stdin and stdout can be used, so that input to COPY can be written by a libpq application and output from COPY can be read by a libpq application.

The BINARY keyword will force all data to be stored/read as binary objects rather than as ASCII text. It is somewhat faster than the normal copy command, but is not generally portable, and the files generated are somewhat larger, although this factor is highly dependent on the data itself. By default, an ASCII copy uses a tab (\t) character as a delimiter. The delimiter may also be changed to any other single character with the keyword USING DELIMITERS. Characters in data fields which happen to match the delimiter character will be quoted.

Notes

You must have select access on any table whose values are read by COPY, and either insert or update access to a table into which values are being inserted by COPY. The backend also needs appropriate Unix permissions for any file read or written by COPY.

The keyword USING DELIMITERS is inaptly named, since only a single character may be specified. (If a group of characters is specified, only the first character is used.)

WARNING: do not confuse COPY with the psql instruction \copy.

Format of output files

ASCII copy format

When COPY is used without BINARY, the file generated will have each instance on a single line, with each attribute separated by the delimiter character. Embedded delimiter characters will be preceded by a backslash character (\). The attribute values themselves are strings generated by the output function associated with each attribute type. The output function for a type should not try to generate the backslash character; this will be handled by COPY itself.

The actual format for each instance is

     <attr1><separator><attr2><separator>...<separator><attrn><newline>
The oid is placed on the beginning of the line if WITH OIDS is specified.

If COPY is sending its output to standard output instead of a file, it will send a backslash(\) and a period (.) followed immediately by a newline, on a separate line, when it is done. Similarly, if COPY is reading from standard input, it will expect a backslash (\) and a period (.) followed by a newline, as the first three characters on a line, to denote end-of-file. However, COPY will terminate (followed by the backend itself) if a true EOF is encountered.

The backslash character has special meaning. NULL attributes are output as \N. A literal backslash character is output as two consecutive backslashes. A literal tab character is represented as a backslash and a tab. A literal newline character is represented as a backslash and a newline. When loading ASCII data not generated by PostgreSQL, you will need to convert backslash characters (\) to double-backslashes (\\) to ensure that they are loaded properly.

Binary copy format

In the case of COPY BINARY, the first four bytes in the file will be the number of instances in the file. If this number is zero, the COPY BINARY command will read until end of file is encountered. Otherwise, it will stop reading when this number of instances has been read. Remaining data in the file will be ignored.

The format for each instance in the file is as follows. Note that this format must be followed exactly. Unsigned four-byte integer quantities are called uint32 in the table below.

Table 1-1. Contents of a binary copy file

At the start of the file
uint32number of tuples
For each tuple
uint32total length of tuple data
uint32oid (if specified)
uint32number of null attributes
[uint32attribute number of first null attribute, counting from 0
......
uint32attribute number of last null attribute]
-<tuple data>

Alignment of binary data

On Sun-3s, 2-byte attributes are aligned on two-byte boundaries, and all larger attributes are aligned on four-byte boundaries. Character attributes are aligned on single-byte boundaries. On other machines, all attributes larger than 1 byte are aligned on four-byte boundaries. Note that variable length attributes are preceded by the attribute's length; arrays are simply contiguous streams of the array element type.

Usage

To copy a table to standard output, using | as a delimiter

 COPY country TO stdout USING DELIMITERS '|';
  

To copy data from a Unix file into a table:

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

A sample of data suitable for copying into a table from stdin (so it has the termination sequence on the last line):

   AF      AFGHANISTAN
   AL      ALBANIA
   DZ      ALGERIA
   ...
   ZM      ZAMBIA
   ZW      ZIMBABWE
   \.
  

The same data, output in binary format on a Linux Intel machine. The data is shown after filtering through od -c. The table has three fields; the first is char(2) and the second is text. All the rows have a null value in the third field). Notice how the char(2) field is padded with nulls to four bytes and the text field is preceded by its length:

   355  \0  \0  \0 027  \0  \0  \0 001  \0  \0  \0 002  \0  \0  \0
   006  \0  \0  \0   A   F  \0  \0 017  \0  \0  \0   A   F   G   H
     A   N   I   S   T   A   N 023  \0  \0  \0 001  \0  \0  \0 002
    \0  \0  \0 006  \0  \0  \0   A   L  \0  \0  \v  \0  \0  \0   A
     L   B   A   N   I   A 023  \0  \0  \0 001  \0  \0  \0 002  \0
    \0  \0 006  \0  \0  \0   D   Z  \0  \0  \v  \0  \0  \0   A   L
     G   E   R   I   A
   ...              \n  \0  \0  \0   Z   A   M   B   I   A 024  \0
    \0  \0 001  \0  \0  \0 002  \0  \0  \0 006  \0  \0  \0   Z   W
    \0  \0  \f  \0  \0  \0   Z   I   M   B   A   B   W   E
  

See also

insert(l), create table(l), vacuum(l), libpq.

Bugs

COPY stops operation at the first error. This should not lead to problems in the event of a copy from, but the target relation will, of course, be partially modified in a copy to. The VACUUM query should be used to clean up after a failed copy.

Because Postgres' current directory is not the same as the user's working directory, the result of copying to a file "foo" (without additional path information) may yield unexpected results for the naive user. In this case, "foo" will wind up in $PGDATA/foo. In general, the full pathname should be used when specifying files to be copied.

Files used as arguments to the copy command must reside on or be accessible to the database server machine by being either on local disks or on a networked file system.

When a TCP/IP connection from one machine to another is used, and a target file is specified, the target file will be written on the machine where the backend is running rather than the user's machine.

Compatibility

SQL92

There is no COPY statement in SQL92.