Databases - Practical PostgreSQL
Previous Page	Home	Next Page

COPY

Name

COPY -- Copies data between files and tables.

Synopsis

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

Parameters

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 used, which is a tab (\t).

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.

Results

COPY: The message returned when a COPY procedure finishes successfully.
ERROR: The error returned if a COPY procedure fails, accompanied by a reason for failure.

Description

Use the COPY command to transfer data between tables in a PostgreSQL database and files within a filesystem. There are two ways to use COPY: 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).

Note: 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 called Adding Data with INSERT and COPY in Chapter 4" 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.

Restrictions and limitations

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.

File formatting

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 follow: 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.

Examples

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

Databases - Practical PostgreSQL
Previous Page	Home	Next Page