COPY FROM

On this page

The COPY FROM statement copies data from cockroach sql or other third party clients to tables in your cluster.

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.
`HEADER`	Specify that CockroachDB should skip the header in CSV data (first line of input).

Required privileges

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

Unsupported syntax

CockroachDB does not yet support the following COPY syntax:

COPY ... TO. To copy data from a CockroachDB cluster to a file, use an EXPORT statement.
- Tracking GitHub Issue
Various COPY options (FORMAT, FREEZE, QUOTE, etc.).
COPY ... FROM ... WHERE <expr>.
- Tracking GitHub Issue

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 to CockroachDB

Start copying data to the users table:
```
COPY users FROM STDIN;
```

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 some tab-delimited data to copy to the table:

Warning:

Before you input the following rows, ensure the delimiters are tab characters. They may have been converted to spaces by the browser.
```
8a3d70a3-d70a-4000-8000-00000000001d    seattle Hannah  '400 Broad St'  0987654321
```
```
9eb851eb-851e-4800-8000-00000000001e    new york    Carl    '53 W 23rd St'  5678901234
```
Mark the end of data with \. on its own line:
```
\.
```
```
COPY 2
```

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 to CockroachDB

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 a header
Copy CSV-delimited data from stdin with hex-encoded byte array data

Copy CSV-delimited data from `stdin`

Create a new table that you will load with CSV-formatted data:
```
CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);
```

Start copying data to the setecastronomy 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 some CSV-delimited data to copy to the table:
```
"My name is Werner Brandes","My voice is my passport"
```
Mark the end of data with \. on its own line:
```
\.
```
```
COPY 1
```

View the data in the setecastronomy table:

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

Create a new table that you will load with CSV-formatted data:
```
CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);
```

Start copying data to the setecastronomy table, specifying an escape character for quoting the fields:

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 some CSV-delimited data to copy to the table:
```
"My name is Werner Brandes","\"My\" \"voice\" \"is\" \"my\" \"passport\""
```
Mark the end of data with \. on its own line:
```
\.
```
```
COPY 1
```

View the data in the setecastronomy table:

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 a header

Create a new table that you will load with CSV-formatted data:
```
CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);
```
Start copying data to the setecastronomy table, specifying that CockroachDB should skip the header (first line of CSV input):
```
COPY setecastronomy FROM STDIN WITH CSV HEADER;
```
Enter the data, including the header line:
```
"name","phrase"
"Hi, my name is Werner Brandes","My voice is my passport; verify me"
```
Mark the end of data with \. on its own line:
```
\.
```
```
COPY 1
```

View the data in the setecastronomy table:

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"
  Hi, my name is Werner Brandes | My voice is my passport; verify me
(3 rows)

Copy CSV-delimited data from `stdin` with hex-encoded byte array data

Create a new table that you will load with CSV-formatted data:
```
CREATE TABLE IF NOT EXISTS mybytes(a INT PRIMARY KEY, b BYTEA);
```
Set the bytea_output session variable to specify that CockroachDB should ingest hex-encoded byte array data:
```
SET bytea_output = 'escape';
```
Start copying data to the mybytes table:
```
COPY mybytes FROM STDIN WITH CSV;
```
Enter some CSV-delimited data to copy to the table:
```
1,X'6869
2,x'6869
3,"\x6869"
4,\x6869
```
Mark the end of data with \. on its own line:
```
\.
```
```
COPY 4
```

View the data in the mybytes table:

SELECT * FROM mybytes;

  a |   b
----+---------
  1 | X'6869
  2 | x'6869
  3 | hi
  4 | hi
(4 rows)

Pricing

Contact us

Sign In

COPY FROM

Syntax

Parameters

Options

Required privileges

Unsupported syntax

Examples

Copy tab-delimited data to CockroachDB

Copy CSV-delimited data to CockroachDB

Copy CSV-delimited data from `stdin`

Copy CSV-delimited data from `stdin` with an escape character

Copy CSV-delimited data from `stdin` with a header

Copy CSV-delimited data from `stdin` with hex-encoded byte array data

See also

COPY FROM

Syntax

Parameters

Options

Required privileges

Unsupported syntax

Examples

Copy tab-delimited data to CockroachDB

Copy CSV-delimited data to CockroachDB

Copy CSV-delimited data from stdin

Copy CSV-delimited data from stdin with an escape character

Copy CSV-delimited data from stdin with a header

Copy CSV-delimited data from stdin with hex-encoded byte array data

See also

Copy CSV-delimited data from `stdin`

Copy CSV-delimited data from `stdin` with an escape character

Copy CSV-delimited data from `stdin` with a header

Copy CSV-delimited data from `stdin` with hex-encoded byte array data