The COPY FROM statement copies data from third-party clients to tables in your cluster.
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.
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.
COPY syntax not supported by CockroachDB
CockroachDB does not yet support the following COPY syntax:
COPY ... TO. To copy data from a CockroachDB cluster to a file, use anEXPORTstatement.COPY ... FROM ... WHERE <expr>
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:
$ 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:
$ 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:
> 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:
>> 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:
> 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)
