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
• Parsers for Various 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 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.