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 anEXPORT
statement.Various unsupported
COPY
options (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)