Introduction to the COPY statement

Use the the COPY statement to load data.

Use the the COPY statement to load data. COPY is a large and versatile statement with many parameters; for all of the details, see the reference page. In its simplest form, COPY copies data from a source to a table, as follows:

=> COPY target-table FROM data-source;

You also use COPY when defining an external table:

=> CREATE EXTERNAL TABLE target-table (...) AS COPY FROM data-source;

Source data can be a data stream or a file path. For more about the FROM clause, see Specifying where to load data from.

You can specify many details about a data load, including:

Global and column-specific options
Data formats and compression
Which built-in parser to use, or which user-defined source, filters, or parser to use
How to distribute the data load among database nodes (Distributing a load)
How to transform data during loading (Transforming data during loads)
What to do with data that could not be loaded (Handling messy data)

For a complete list of parameters, see Parameters.

Permissions

Generally, only a superuser can use the COPY statement to bulk-load data. Non-supersuers can use COPY in certain cases:

To load from a stream on the host (such as STDIN) rather than a file (see Streaming data via JDBC).
To load with the FROM LOCAL option.
To load into a storage location where the user has been granted permission.
To use a user-defined-load function for which the user has permission.

A non-superuser can also perform a batch load with a JDBC prepared statement, which invokes COPY to load data as a background task.

Users must also have read permission to the source from which the data is to be loaded.