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

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.