copy.7

PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开发团队说,该版本将加速更多企业向该数据库移植.核心开发成员之一Bruce Momjian表示,在新版PostgreSQL

.\\" auto-generated by docbook2man-spec $Revision: 1.1.1.1 $
.TH "COPY" "" "2008-01-03" "SQL - Language Statements" "SQL Commands"
.SH NAME
COPY \- copy data between a file and a table

.SH SYNOPSIS
.sp
.nf
COPY \fItablename\fR [ ( \fIcolumn\fR [, ...] ) ]
    FROM { '\fIfilename\fR' | STDIN }
    [ [ WITH ] 
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] '\fIdelimiter\fR' ]
          [ NULL [ AS ] '\fInull string\fR' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] '\fIquote\fR' ] 
                [ ESCAPE [ AS ] '\fIescape\fR' ]
                [ FORCE NOT NULL \fIcolumn\fR [, ...] ]

COPY { \fItablename\fR [ ( \fIcolumn\fR [, ...] ) ] | ( \fIquery\fR ) }
    TO { '\fIfilename\fR' | STDOUT }
    [ [ WITH ] 
          [ BINARY ]
          [ HEADER ]
          [ OIDS ]
          [ DELIMITER [ AS ] '\fIdelimiter\fR' ]
          [ NULL [ AS ] '\fInull string\fR' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] '\fIquote\fR' ] 
                [ ESCAPE [ AS ] '\fIescape\fR' ]
                [ FORCE QUOTE \fIcolumn\fR [, ...] ]
.sp
.fi
.SH "DESCRIPTION"
.PP
\fBCOPY\fR moves data between
PostgreSQL tables and standard file-system
files. \fBCOPY TO\fR copies the contents of a table
\fBto\fR a file, while \fBCOPY FROM\fR copies
data \fBfrom\fR a file to a table (appending the data to
whatever is in the table already). \fBCOPY TO\fR
can also copy the results of a \fBSELECT\fR query.
.PP
If a list of columns is specified, \fBCOPY\fR 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,
\fBCOPY FROM\fR will insert the default values for
those columns.
.PP
\fBCOPY\fR 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.
.SH "PARAMETERS"
.TP
\fB\fItablename\fB\fR
The name (optionally schema-qualified) of an existing table.
.TP
\fB\fIcolumn\fB\fR
An optional list of columns to be copied. If no column list is
specified, all columns of the table will be copied.
.TP
\fB\fIquery\fB\fR
A SELECT [\fBselect\fR(7)] or
VALUES [\fBvalues\fR(7)] command
whose results are to be copied.
Note that parentheses are required around the query.
.TP
\fB\fIfilename\fB\fR
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.
.TP
\fBSTDIN\fR
Specifies that input comes from the client application.
.TP
\fBSTDOUT\fR
Specifies that output goes to the client application.
.TP
\fBBINARY\fR
Causes all data to be stored or read in binary format rather
than as text. You cannot specify the \fBDELIMITER\fR,
\fBNULL\fR, or \fBCSV\fR options in binary mode.
.TP
\fBOIDS\fR
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 \fIquery\fR.)
.TP
\fB\fIdelimiter\fB\fR
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.
.TP
\fB\fInull string\fB\fR
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.
.sp
.RS
.B "Note:"
When using \fBCOPY FROM\fR, 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
\fBCOPY TO\fR.
.RE
.sp
.TP
\fBCSV\fR
Selects Comma Separated Value (CSV) mode.
.TP
\fBHEADER\fR
Specifies 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.
.TP
\fB\fIquote\fB\fR
Specifies the ASCII quotation character in CSV mode.
The default is double-quote.
.TP
\fB\fIescape\fB\fR
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).
.TP
\fBFORCE QUOTE\fR
In CSV \fBCOPY TO\fR mode, forces quoting to be
used for all non-NULL values in each specified column.
NULL output is never quoted.
.TP
\fBFORCE NOT NULL\fR
In CSV \fBCOPY FROM\fR 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.
.SH "OUTPUTS"
.PP
On successful completion, a \fBCOPY\fR command returns a command
tag of the form
.sp
.nf
COPY \fIcount\fR
.sp
.fi
The \fIcount\fR is the number
of rows copied.
.SH "NOTES"
.PP
\fBCOPY\fR can only be used with plain tables, not
with views. However, you can write COPY (SELECT * FROM
\fIviewname\fR) TO ....
.PP
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.
.PP
You must have select privilege on the table
whose values are read by \fBCOPY TO\fR, and
insert privilege on the table into which values
are inserted by \fBCOPY FROM\fR.
.PP
Files named in a \fBCOPY\fR 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. \fBCOPY\fR naming a
file is only allowed to database superusers, since it allows reading
or writing any file that the server has privileges to access.
.PP
Do not confuse \fBCOPY\fR with the
\fBpsql\fR instruction
\fB\\copy\fR. \fB\\copy\fR invokes
\fBCOPY FROM STDIN\fR or \fBCOPY TO
STDOUT\fR, and then fetches/stores the data in a file
accessible to the \fBpsql\fR client. Thus,
file accessibility and access rights depend on the client rather
than the server when \fB\\copy\fR is used.
.PP
It is recommended that the file name used in \fBCOPY\fR
always be specified as an absolute path. This is enforced by the
server in the case of \fBCOPY TO\fR, but for
\fBCOPY FROM\fR 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 (normally
the cluster's data directory), not the client's working directory.
.PP
\fBCOPY FROM\fR will invoke any triggers and check
constraints on the destination table. However, it will not invoke rules.
.PP
\fBCOPY\fR 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 \fBCOPY TO\fR.
.PP
\fBCOPY\fR stops operation at the first error. This
should not lead to problems in the event of a \fBCOPY
TO\fR, but the target table will already have received
earlier rows in a \fBCOPY FROM\fR. These rows will not
be visible or accessible, but they still occupy disk space. This may
amount to a considerable amount of wasted disk space if the failure
happened well into a large copy operation. You may wish to invoke
\fBVACUUM\fR to recover the wasted space.
.SH "FILE FORMATS"
.SS "TEXT FORMAT"
.PP
When \fBCOPY\fR 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.
\fBCOPY FROM\fR 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.
.PP
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.
.PP
Backslash characters (\\) may be used in the
\fBCOPY\fR data to quote data characters that might
otherwise be taken as row or column delimiters. In particular, the
following characters \fBmust\fR be preceded by a backslash if
they appear as part of a column value: backslash itself,
newline, carriage return, and the current delimiter character.
.PP
The specified null string is sent by \fBCOPY TO\fR without
adding any backslashes; conversely, \fBCOPY FROM\fR 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).
.PP
The following special backslash sequences are recognized by
\fBCOPY FROM\fR:
SequenceRepresents\\bBackspace (ASCII 8)\\fForm feed (ASCII 12)\\nNewline (ASCII 10)\\rCarriage return (ASCII 13)\\tTab (ASCII 9)\\vVertical tab (ASCII 11)\\\fIdigits\fRBackslash followed by one to three octal digits specifies
the character with that numeric code\\x\fIdigits\fRBackslash x followed by one or two hex digits specifies
the character with that numeric code
Presently, \fBCOPY TO\fR will never emit an octal or 
hex-digits backslash sequence, but it does use the other sequences
listed above for those control characters.
.PP
Any other backslashed character that is not mentioned in the above table
will be taken to represent itself. However, beware of adding backslashes