COPY FROM

On this page Carat arrow pointing down
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.

The COPY FROM statement copies data from cockroach sql or other 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.

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:

Examples

To run the examples, use cockroach demo to start a temporary, in-memory cluster with the movr database preloaded.

icon/buttons/copy
cockroach demo

Copy tab delimited data

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

icon/buttons/copy
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.

Note:

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:

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
--------------------------------------+----------+--------+----------------+-------------
 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

Run the following SQL statement to create a new table that you will load with CSV formatted data:

icon/buttons/copy
CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);

Run the following command to start copying data to the table:

icon/buttons/copy
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:

icon/buttons/copy
"My name is Werner Brandes","My voice is my passport"
icon/buttons/copy
\.
COPY 1

To view the data, enter the following query:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
"My name is Werner Brandes","\"My\" \"voice\" \"is\" \"my\" \"passport\""
icon/buttons/copy
\.
COPY 1

To view the data, enter the following query:

icon/buttons/copy
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:

icon/buttons/copy
CREATE TABLE IF NOT EXISTS mybytes(a INT PRIMARY KEY, b BYTEA);
icon/buttons/copy
set bytea_output = 'escape';

To import the data, enter the following statement:

icon/buttons/copy
COPY mybytes FROM STDIN WITH CSV;

Enter the data, followed by a backslash and period on a line by itself:

icon/buttons/copy
1,X'6869
2,x'6869
3,"\x6869"
4,\x6869
icon/buttons/copy
\.
COPY 4

To view the data, enter the following query:

icon/buttons/copy
SELECT * FROM mybytes;
  a |   b
----+---------
  1 | X'6869
  2 | x'6869
  3 | hi
  4 | hi
(4 rows)

See also


Yes No
On this page

Yes No