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:

For a complete list of parameters, see COPY 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.