COPY

On this page Carat arrow pointing down

The COPY ... FROM statement copies data from cockroach sql or other third party clients to tables in your cluster. The COPY ... TO statement allows you to export a table or arbitrary query in a text or CSV format.

Syntax

COPY table_name opt_column_list FROM STDIN TO STDOUT ( query ) TO STDOUT WITH copy_options

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.
query A SELECT, INSERT, UPDATE, UPSERT, or DELETE statement for which to copy results.

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, or copy data TO 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 ... WITH FREEZE. #85573
  • COPY ... WITH QUOTE. #85574
  • COPY ... FROM ... WHERE <expr>. #54580

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

  1. Start copying data to the users table:

    icon/buttons/copy
    COPY users FROM STDIN;
    
  2. 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.
    
  3. 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
    
  4. Mark the end of data with \. on its own line:

    \.
    
    COPY 2
    
  5. 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 to CockroachDB

You can copy CSV data into CockroachDB using the following methods:

Copy CSV-delimited data from stdin

  1. 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);
    
  2. Start copying data to the setecastronomy 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.
    
  3. Enter some CSV-delimited data to copy to the table:

    icon/buttons/copy
    "My name is Werner Brandes","My voice is my passport"
    
  4. Mark the end of data with \. on its own line:

    icon/buttons/copy
    \.
    
    COPY 1
    
  5. View the data in the setecastronomy table:

    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

  1. 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);
    
  2. Start copying data to the setecastronomy table, specifying an escape character for quoting the fields:

    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.
    
  3. Enter some CSV-delimited data to copy to the table:

    icon/buttons/copy
    "My name is Werner Brandes","\"My\" \"voice\" \"is\" \"my\" \"passport\""
    
  4. Mark the end of data with \. on its own line:

    icon/buttons/copy
    \.
    
    COPY 1
    
  5. View the data in the setecastronomy table:

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

  1. 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);
    
  2. Start copying data to the setecastronomy table, specifying that CockroachDB should skip the header (first line of CSV input):

    icon/buttons/copy
    COPY setecastronomy FROM STDIN WITH CSV HEADER;
    
  3. Enter the data, including the header line:

    icon/buttons/copy
    "name","phrase"
    "Hi, my name is Werner Brandes","My voice is my passport; verify me"
    
  4. Mark the end of data with \. on its own line:

    icon/buttons/copy
    \.
    
    COPY 1
    
  5. View the data in the setecastronomy table:

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

  1. Create a new table that you will load with CSV-formatted data:

    icon/buttons/copy
    CREATE TABLE IF NOT EXISTS mybytes(a INT PRIMARY KEY, b BYTEA);
    
  2. Set the bytea_output session variable to specify that CockroachDB should ingest hex-encoded byte array data:

    icon/buttons/copy
    SET bytea_output = 'escape';
    
  3. Start copying data to the mybytes table:

    icon/buttons/copy
    COPY mybytes FROM STDIN WITH CSV;
    
  4. Enter some CSV-delimited data to copy to the table:

    icon/buttons/copy
    1,X'6869
    2,x'6869
    3,"\x6869"
    4,\x6869
    
  5. Mark the end of data with \. on its own line:

    icon/buttons/copy
    \.
    
    COPY 4
    
  6. View the data in the mybytes table:

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

Copy data to stdout in CSV format

  1. Start a temporary, in-memory cluster with the movr sample dataset preloaded:

  2. Copy five rows from the users table to stdout, specifying the CSV option:

    icon/buttons/copy
    COPY (SELECT * FROM users LIMIT 5) TO STDOUT WITH CSV;
    
    ae147ae1-47ae-4800-8000-000000000022,amsterdam,Christine Crosby,10563 Mcfarland Burg Apt. 34,1007823073
    b3333333-3333-4000-8000-000000000023,amsterdam,Natalie Barnes,58875 Monique Port,4777504042
    b851eb85-1eb8-4000-8000-000000000024,amsterdam,Brenda Meyer,82208 Jamie Track Suite 57,3209048436
    bd70a3d7-0a3d-4000-8000-000000000025,amsterdam,Jose Nelson,31068 Mark Mall,2715842506
    c28f5c28-f5c2-4000-8000-000000000026,amsterdam,Anna Bennett,80284 Jeffery Courts,1695553015
    

See also


Yes No
On this page

Yes No