COPY (l)
Copies data between files and tables
SYNOPSIS
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ USING DELIMITERS 'delimiter' ]
COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ USING DELIMITERS 'delimiter' ]
BINARY
Changes the behavior of field formatting, forcing all data to be
stored or read as binary objects rather than as text.
table
The name of an existing table.
WITH OIDS
Copies the internal unique object id (OID) for each row.
filename
The absolute Unix pathname of the input or output file.
stdin
Specifies that input comes from a pipe or terminal.
stdout
Specifies that output goes to a pipe or terminal.
delimiter
A character that delimits the input or output fields.
COPY
The copy completed successfully.
ERROR: reason
The copy failed for the reason stated in the error message.
DESCRIPTION
COPY moves data between
Postgres tables and
standard file-system files.
COPY instructs
the Postgres backend
to directly read from or write to a file. The file must be directly visible to
the backend and the name must be specified from the viewpoint of the backend.
If stdin or stdout are
specified, data flows through the client frontend to the backend.
The BINARY keyword will force all data to be
stored/read as binary objects rather than as 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, a text copy uses a tab ("\\t") character as a delimiter.
The delimiter may also be changed to any other single character
with the keyword phrase USING DELIMITERS. Characters
in data fields which happen to match the delimiter character will
be quoted.
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 phrase USING DELIMITERS specifies a single character
to be used for all delimiters between columns. If multiple characters
are specified in the delimiter string, only the first character is
used.
Tip:
Do not confuse COPY with the
psql instruction \\copy.
COPY neither invokes rules nor acts on column defaults.
It does invoke triggers, however.
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.
VACUUM should be used to clean up
after a failed copy.
Because the Postgres backend's current working directory
is not usually 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 as it would appear to the backend server machine
should be used when specifying files to
be copied.
Files used as arguments to COPY
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.
FILE FORMATS
When COPY TO is used without the BINARY option,
the file generated will have each row (instance) on a single line, with each
column (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 before this special end-of-file pattern is found.
The backslash character has other special meanings. NULL attributes are
represented as "\\N". A literal backslash character is represented 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 text data
not generated by Postgres,
you will need to convert backslash
characters ("\\") to double-backslashes ("\\\\") to ensure that they are loaded
properly.
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.
"CONTENTS OF A BINARY COPY FILE"
At the start of the fileuint32number of tuplesFor each tupleuint32total length of tuple datauint32oid (if specified)uint32number of null attributes[uint32,...,uint32]attribute numbers of attributes, counting from 0-<tuple 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
most 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
The following example copies a table to standard output,
using a vertical bar ("|") as the field
delimiter:
COPY country TO stdout USING DELIMITERS '|';
To copy data from a Unix file into a table "country":
COPY country FROM '/usr1/proj/bray/sql/country_data';
Here is 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/i586 machine.
The data is shown after filtering through
the Unix utility 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
COMPATIBILITY
|