COPY FROM

On this page Carat arrow pointing down
Warning:
CockroachDB v21.2 is no longer supported as of May 16, 2023. For more details, refer to the Release Support Policy.

The COPY FROM statement copies data from third-party clients to tables in your cluster.

Warning:

By default, COPY FROM statements are segmented into batches of 100 rows. If any row encounters an error, only the rows that precede the failed row remain committed.

If you need COPY FROM statements to commit atomically, issue the statements within an explicit transaction.

Note:

CockroachDB currently only supports COPY FROM statements issued from third-party clients, for compatibility with PostgreSQL drivers and ORMs. COPY FROM statements cannot be issued from the cockroach SQL shell. To copy data from a file to your cluster, we recommend using an IMPORT statement instead.

Syntax

Parameters

Parameter Description
table_name The name of the table to which to copy data.
opt_column_list The column name, or list of column names, to which to copy data.
WITH copy_options Optionally specify one or more copy options.

Options

Option Description
DELIMITER 'value' The value that delimits the rows of input data, passed as a string.
NULL 'value' The string that represents a NULL value in the input data.
BINARY Copy data from binary format. If BINARY is specified, no other format can be specified.
If no format is specified, CockroachDB copies in plaintext format.
CSV Copy data from CSV format. If CSV is specified, no other format can be specified.
If no format is specified, CockroachDB copies in plaintext format.
ESCAPE Specify an escape character for quoting the fields in CSV data.

Required privileges

Only members of the admin role can run COPY statements. By default, the root user belongs to the admin role.

Known limitations

COPY FROM statements are not supported in the CockroachDB SQL shell

The built-in SQL shell provided with CockroachDB (cockroach sql / cockroach demo) does not currently support importing data with the COPY statement.

To load data into CockroachDB, we recommend that you use an IMPORT. If you must use a COPY statement, you can issue the statement from the psql client command provided with PostgreSQL, or from another third-party client.

Tracking GitHub Issue

COPY syntax not supported by CockroachDB

CockroachDB does not yet support the following COPY syntax:

Example

The following example copies data from the PostgreSQL psql client into a demo CockroachDB cluster. To follow along, make sure that you have PostgreSQL installed.

Run cockroach demo to start a temporary, in-memory cluster with the movr database preloaded:

icon/buttons/copy
$ cockroach demo

Take note of the (sql) connection string listed under Connection parameters in the welcome message of the demo cluster's SQL shell:

# Connection parameters:
...
#   (sql) postgres://demo:demo11762@127.0.0.1:26257?sslmode=require

Open a new terminal window, and connect to your demo cluster with psql, using the connection string provided for the demo cluster, with the movr database specified:

icon/buttons/copy
$ psql postgres://demo:demo11762@127.0.0.1:26257?sslmode=require

In the psql shell, run the following command to start copying data from psql to the users table:

icon/buttons/copy
> movr=# COPY users FROM STDIN;

The following prompt should appear:

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.

Enter some tab-delimited data that you want copied to the users table:

icon/buttons/copy
>> 8a3d70a3-d70a-4000-8000-00000000001d seattle Hannah  400 Broad St    0987654321
>> 9eb851eb-851e-4800-8000-00000000001e new york    Carl    53 W 23rd St    5678901234
>> \.
COPY 2

In the demo cluster's shell, query the users table for the rows that you just inserted:

icon/buttons/copy
> SELECT * FROM users WHERE id IN ('8a3d70a3-d70a-4000-8000-00000000001d', '9eb851eb-851e-4800-8000-00000000001e');
                   id                  |   city   |  name  |   address    | credit_card
---------------------------------------+----------+--------+--------------+--------------
  8a3d70a3-d70a-4000-8000-00000000001d | seattle  | Hannah | 400 Broad St | 0987654321
  9eb851eb-851e-4800-8000-00000000001e | new york | Carl   | 53 W 23rd St | 5678901234
(2 rows)

See also


Yes No
On this page

Yes No