Copies data between files and tables.
COPY [ BINARY ] table [ WITH OIDS ] FROM { 'filename' | stdin } [ [ USING ] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null_string' ] COPY [ BINARY ] table [ WITH OIDS ] TO { 'filename' | stdout } [ [ USING ] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null_string' ]
BINARY
The BINARY
mode keyword. This specifies that you wish for COPY
to store and read data in PostgreSQL’s own binary format (rather than
text). When using binary format, the WITH NULL
and DELIMITERS
keywords are not applicable.
table
The name of an existing table to either copy data from, or to.
FROM
The FROM
keyword, which indicates that the COPY
operation will copy data from either a file or stdin
into table
.
TO
The TO
keyword, which indicates that the COPY
operation will copy data to either a file or stdout
, from the data in table
.
WITH OIDS
The optional oid
specifier. This indicates that the copy operation
is to either select or insert the unique object ID (oid
) of each row,
depending on whether or not it is a COPY FROM
or COPY
TO
.
filename
The absolute path to the file chosen for input or output (i.e., /usr/local/pgsql/data/employeetable). You must specify an absolute path.
stdin
The standard input option. When used in place of a filename,
this indicates that data will be accepted from the client application, rather than a file.
If you are using psql to enter data, you will be prompted to type in
text if you initiate a COPY FROM
on stdin
.
stdout
The standard output option. When used in place of a filename, this indicates that output will be sent directly to the client, rather than to a file (e.g., to psql).
delimiter
The character symbol that separates column values in a row. On a COPY
FROM
, PostgreSQL will expect this character to delimit columns. On a COPY TO
, PostgreSQL will delimit each column value by this character in its
output. If omitted, the default delimiter is used, which is a tab (
).
The delimiter you choose must only be one character; if you enter something longer than one character, only the first character of what you enter will be used as the delimiter.
null_string
The character sequence used to identify a NULL
value. By default,
N
is used, but you can change it to something more suited to your
needs. Recognize that when data is copied into the database, any strings that match your
specified NULL
string will be interpreted as NULL
values, so make sure to use the same string when you copy data in to the database as you
used when you copied the data out to begin with, and try to choose a sequence that should
never have a valid, non-NULL
counterpart.
Use the COPY
command to transfer data between tables in a PostgreSQL
database and files within a filesystem. There are two ways to use it: COPY
TO
and COPY FROM
.
Use COPY TO
when you want to output the entire contents of a table in
your database to a file, or to standard output (stdout, i.e., the client
connected to the database). Use COPY FROM
when you wish to import data from
a standard file, or standard input (stdin, from the client).
The SQL COPY
command should not be confused with the
psql
copy
command. copy
performs a COPY FROM
stdin
or COPY TO stdout
, storing the acquired data in a
psql-accessible file. This means the file access rights are controlled
by the client (frontend), instead of the postmaster (backend).
See the section titled Adding Data with INSERT and COPY in Chapter 4, for more on the nuances of this command.
You can either use normal text for transferring data, or you can use binary format (when
specified with the BINARY
keyword). Using binary format will speed up
COPY
commands significantly; however, binary formatting decreases the
portability of your application due to low-level byte ordering differences.
There are a few restrictions to the COPY
command. In order for
COPY
to read from the tables specified, your PostgreSQL user must have
SELECT
access granted on them. If you are directing COPY
to insert values into a table, your PostgreSQL user must also have INSERT
or UPDATE
access.
Likewise, if you are copying files to or from a system file, the user running the
PostgreSQL backend (the postmaster) must have the necessary filesystem
permissions on the specified file. To sidestep this restriction, the copy
command exists within psql (see Chapter 6, for
more on this).
Using the COPY TO
command will check constraints, and any triggers you
may have set up, but it will not invoke rules or act on column defaults.
COPY
will stop operation upon reaching the first error. This should
not lead to problems in the event of a COPY FROM
, but the target relation
will be partially modified in a COPY TO
. The VACUUM
command should be used to clean up after a failed COPY
command.
If you are a developer interested in the technical format of PostgreSQL’s binary output,
see Appendix C. If you choose to use normal text formatting instead
of binary, the file COPY
creates will be formatted as such: each row will
appear on a single line, with column values separated by the delimiter
character. Any embedded characters located in the file have
a preceding backslash (), and column values are output as plain text
strings.
If WITH OIDS
is specified, the oid will be placed at the beginning of
the line. If you create an output file using the WITH OIDS
clause, and wish
to import that data back into PostgreSQL (or on another PostgreSQL machine), remember to again
specify the WITH OIDS
clause. Otherwise, it will be interpreted as a normal
column.
When COPY
sends its results to standard output
(stdout), it will end the transfer with the following format: a
backslash (), a period (
.
), and a newline to mark the
end of the file. If an EOF (end of file) is reached before the normal end-format, it will
automatically terminate.
Due to the backslash character having multiple definitions, you’ll need to use an escape
sequence to represent an actual backslash character. Do this by typing two consecutive
backslashes (\
). The other characters that require this method to display
correctly are as follows: the tab character is represented as a backslash and a tab, and a
newline is represented by a backslash and a newline.
Remember to pre-format any non-PostgreSQL text data that you are loading into the database by changing backslashes to double-backslashes.
The example below copies the employees
table to the emp_table
file, using a pipe (|
) as the field delimiter:
booktown=# COPY employees TO '/tmp/employee_data' USING DELIMITERS '|';
COPY
The following example copies data from a system file into the publishers
table:
booktown=# COPY publishers FROM '/tmp/publisher_data';
COPY
3.14.246.148