COPY (7) Linux Manual Page

COPY – copy data between a file and a table

Synopsis

COPY tablename [ ( column [, ...] ) ]
 FROM { 'filename' | STDIN }
 [ [ WITH ] 
 [ BINARY ]
 [ OIDS ]
 [ DELIMITER [ AS ] 'delimiter' ]
 [ NULL [ AS ] 'null string' ]
 [ CSV [ HEADER ]
 [ QUOTE [ AS ] 'quote' ] 
 [ ESCAPE [ AS ] 'escape' ]
 [ FORCE NOT NULL column [, ...] ]
COPY { tablename [ ( column [, ...] ) ] | ( query ) }
 TO { 'filename' | STDOUT }
 [ [ WITH ] 
 [ BINARY ]
 [ OIDS ]
 [ DELIMITER [ AS ] 'delimiter' ]
 [ NULL [ AS ] 'null string' ]
 [ CSV [ HEADER ]
 [ QUOTE [ AS ] 'quote' ] 
 [ ESCAPE [ AS ] 'escape' ]
 [ FORCE QUOTE column [, ...] ]

Description

COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.

If a list of columns is specified, COPY 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, COPY FROM will insert the default values for those columns.

COPY 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.

Parameters

tablename

The name (optionally schema-qualified) of an existing table.

column

An optional list of columns to be copied. If no column list is specified, all columns of the table will be copied.

query

A SELECT [select(7)] or VALUES [values(7)] command whose results are to be copied. Note that parentheses are required around the query.

filename

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.

STDIN

Specifies that input comes from the client application.

STDOUT

Specifies that output goes to the client application.

BINARY

Causes all data to be stored or read in binary format rather than as text. You cannot specify the DELIMITER, NULL, or CSV options in binary mode.

OIDS

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 query.)

delimiter

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.

null string

The string that represents a null value. The default is \N (backslash-N) in text mode, and an unquoted empty string 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.

Note: When using COPY FROM, 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 COPY TO.

CSV

Selects Comma Separated Value (CSV) mode.

HEADER

Specifies that 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.

quote

Specifies the ASCII quotation character in CSV mode. The default is double-quote.

escape

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).

FORCE QUOTE

In CSV COPY TO mode, forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted.

FORCE NOT NULL

In CSV COPY FROM 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.

Outputs

On successful completion, a COPY command returns a command tag of the form

COPY count

The count is the number of rows copied.

Notes

COPY can only be used with plain tables, not with views. However, you can write COPY (SELECT * FROM viewname) TO ….

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. Also, the binary format is very data type specific; for example it will not work to output binary data from a smallint column and read it into an integer column, even though that would work fine in text format.

You must have select privilege on the table whose values are read by COPY TO, and insert privilege on the table into which values are inserted by COPY FROM. It is sufficient to have column privileges on the column(s) listed in the command.

Files named in a COPY 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. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

Do not confuse COPY with the psql instruction