The COPY FROM statement copies data from cockroach sql or other 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.
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 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.Various unsupported
COPYoptions (FORMAT,FREEZE, etc.)COPY ... FROM ... WHERE <expr>
Examples
To run the examples, use cockroach demo to start a temporary, in-memory cluster with the movr database preloaded.
cockroach demo
Copy tab delimited data
In the SQL shell, run the following command to start copying data to the users table:
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.
You may need to edit the following rows after copying them to make sure the delimiters are tab characters.
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 SQL 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
--------------------------------------+----------+--------+----------------+-------------
9eb851eb-851e-4800-8000-00000000001e | new york | Carl | '53 W 23rd St' | 5678901234
8a3d70a3-d70a-4000-8000-00000000001d | seattle | Hannah | '400 Broad St' | 0987654321
(2 rows)
Copy CSV delimited data
You can copy CSV data into CockroachDB using the following methods:
- Copy CSV delimited data from stdin
- Copy CSV delimited data from stdin with an escape character
- Copy CSV delimited data from stdin with hex encoded byte array data
Copy CSV delimited data from stdin
Run the following SQL statement to create a new table that you will load with CSV formatted data:
CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);
Run the following command to start copying data to the table:
COPY setecastronomy FROM STDIN WITH CSV;
You will see the following prompt:
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 the data, followed by a backslash and period on a line by itself:
"My name is Werner Brandes","My voice is my passport"
\.
COPY 1
To view the data, enter the following query:
SELECT * FROM setecastronomy;
name | phrase
----------------------------+------------------------------------
My name is Werner Brandes | My voice is my passport
(1 row)
Copy CSV delimited data from stdin with an escape character
Run the following SQL statement to create a new table that you will load with CSV formatted data:
CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);
To copy CSV data into CockroachDB and specify an escape character for quoting the fields, enter the following statement:
COPY setecastronomy FROM STDIN WITH CSV DELIMITER ',' ESCAPE '\';
You will see the following prompt:
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 the data, followed by a backslash and period on a line by itself:
"My name is Werner Brandes","\"My\" \"voice\" \"is\" \"my\" \"passport\""
\.
COPY 1
To view the data, enter the following query:
SELECT * FROM setecastronomy;
name | phrase
----------------------------+------------------------------------
My name is Werner Brandes | My voice is my passport
My name is Werner Brandes | "My" "voice" "is" "my" "passport"
(2 rows)
Copy CSV delimited data from stdin with hex encoded byte array data
To copy CSV data into CockroachDB and specify that CockroachDB should ingest hex encoded byte array data, enter the following statements:
CREATE TABLE IF NOT EXISTS mybytes(a INT PRIMARY KEY, b BYTEA);
set bytea_output = 'escape';
To import the data, enter the following statement:
COPY mybytes FROM STDIN WITH CSV;
Enter the data, followed by a backslash and period on a line by itself:
1,X'6869
2,x'6869
3,"\x6869"
4,\x6869
\.
COPY 4
To view the data, enter the following query:
SELECT * FROM mybytes;
a | b
----+---------
1 | X'6869
2 | x'6869
3 | hi
4 | hi
(4 rows)
