Introduction to the COPY statement

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 file, 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.