Examples

Read a CSV file into the lineitem table, using auto-detected CSV options:

COPY lineitem FROM 'lineitem.csv';

Read a CSV file into the lineitem table, using manually specified CSV options:

COPY lineitem FROM 'lineitem.csv' (DELIMITER '|');

Read a Parquet file into the lineitem table:

COPY lineitem FROM 'lineitem.pq' (FORMAT PARQUET);

Read a JSON file into the lineitem table, using auto-detected options:

COPY lineitem FROM 'lineitem.json' (FORMAT JSON, AUTO_DETECT true);

Read a CSV file into the lineitem table, using double quotes:

COPY lineitem FROM "lineitem.csv";

Read a CSV file into the lineitem table, omitting quotes:

COPY lineitem FROM lineitem.csv;

Write a table to a CSV file:

COPY lineitem TO 'lineitem.csv' (FORMAT CSV, DELIMITER '|', HEADER);

Write a table to a CSV file, using double quotes:

COPY lineitem TO "lineitem.csv";

Write a table to a CSV file, omitting quotes:

COPY lineitem TO lineitem.csv;

Write the result of a query to a Parquet file:

COPY (SELECT l_orderkey, l_partkey FROM lineitem) TO 'lineitem.parquet' (COMPRESSION ZSTD);

Copy the entire content of database db1 to database db2:

COPY FROM DATABASE db1 TO db2;

Copy only the schema (catalog elements) but not any data:

COPY FROM DATABASE db1 TO db2 (SCHEMA);

Overview

COPY moves data between DuckDB and external files. COPY ... FROM imports data into DuckDB from an external file. COPY ... TO writes data from DuckDB to an external file. The COPY command can be used for CSV, PARQUET and JSON files.

COPY ... FROM

COPY ... FROM imports data from an external file into an existing table. The data is appended to whatever data is in the table already. The amount of columns inside the file must match the amount of columns in the table table_name, and the contents of the columns must be convertible to the column types of the table. In case this is not possible, an error will be thrown.

If a list of columns is specified, COPY will only copy the data in the specified columns from the file. If there are any columns in the table that are not in the column list, COPY ... FROM will insert the default values for those columns

Copy the contents of a comma-separated file test.csv without a header into the table test:

COPY test FROM 'test.csv';

Copy the contents of a comma-separated file with a header into the category table:

COPY category FROM 'categories.csv' (HEADER);

Copy the contents of lineitem.tbl into the lineitem table, where the contents are delimited by a pipe character (|):

COPY lineitem FROM 'lineitem.tbl' (DELIMITER '|');

Copy the contents of lineitem.tbl into the lineitem table, where the delimiter, quote character, and presence of a header are automatically detected:

COPY lineitem FROM 'lineitem.tbl' (AUTO_DETECT true);

Read the contents of a comma-separated file names.csv into the name column of the category table. Any other columns of this table are filled with their default value:

COPY category(name) FROM 'names.csv';

Read the contents of a Parquet file lineitem.parquet into the lineitem table:

COPY lineitem FROM 'lineitem.parquet' (FORMAT PARQUET);

Read the contents of a newline-delimited JSON file lineitem.ndjson into the lineitem table:

COPY lineitem FROM 'lineitem.ndjson' (FORMAT JSON);

Read the contents of a JSON file lineitem.json into the lineitem table:

COPY lineitem FROM 'lineitem.json' (FORMAT JSON, ARRAY true);

Syntax

COPY ... TO

COPY ... TO exports data from DuckDB to an external CSV or Parquet file. It has mostly the same set of options as COPY ... FROM, however, in the case of COPY ... TO the options specify how the file should be written to disk. Any file created by COPY ... TO can be copied back into the database by using COPY ... FROM with a similar set of options.

The COPY ... TO function can be called specifying either a table name, or a query. When a table name is specified, the contents of the entire table will be written into the resulting file. When a query is specified, the query is executed and the result of the query is written to the resulting file.

Copy the contents of the lineitem table to a CSV file with a header:

COPY lineitem TO 'lineitem.csv';

Copy the contents of the lineitem table to the file lineitem.tbl, where the columns are delimited by a pipe character (|), including a header line:

COPY lineitem TO 'lineitem.tbl' (DELIMITER '|');

Use tab separators to create a TSV file without a header:

COPY lineitem TO 'lineitem.tsv' (DELIMITER '\t', HEADER false);

Copy the l_orderkey column of the lineitem table to the file orderkey.tbl:

COPY lineitem(l_orderkey) TO 'orderkey.tbl' (DELIMITER '|');

Copy the result of a query to the file query.csv, including a header with column names:

COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.csv' (DELIMITER ',');

Copy the result of a query to the Parquet file query.parquet:

COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.parquet' (FORMAT PARQUET);

Copy the result of a query to the newline-delimited JSON file query.ndjson:

COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.ndjson' (FORMAT JSON);

Copy the result of a query to the JSON file query.json:

COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.json' (FORMAT JSON, ARRAY true);

COPY ... TO Options

Zero or more copy options may be provided as a part of the copy operation. The WITH specifier is optional, but if any options are specified, the parentheses are required. Parameter values can be passed in with or without wrapping in single quotes.

Any option that is a Boolean can be enabled or disabled in multiple ways. You can write true, ON, or 1 to enable the option, and false, OFF, or 0 to disable it. The BOOLEAN value can also be omitted, e.g., by only passing (HEADER), in which case true is assumed.

The below options are applicable to all formats written with COPY.

Syntax

COPY FROM DATABASE ... TO

The COPY FROM DATABASE ... TO statement copies the entire content from one attached database to another attached database. This includes the schema, including constraints, indexes, sequences, macros, and the data itself.

ATTACH 'db1.db' AS db1;
CREATE TABLE db1.tbl AS SELECT 42 AS x, 3 AS y;
CREATE MACRO db1.two_x_plus_y(x, y) AS 2 * x + y;

ATTACH 'db2.db' AS db2;
COPY FROM DATABASE db1 TO db2;
SELECT db2.two_x_plus_y(x, y) AS z FROM db2.tbl;

To only copy the schema of db1 to db2 but omit copying the data, add SCHEMA to the statement:

COPY FROM DATABASE db1 TO db2 (SCHEMA);

Syntax

Format-Specific Options

CSV Options

The below options are applicable when writing CSV files.

Parquet Options

The below options are applicable when writing Parquet files.

Some examples of FIELD_IDS are as follows.

Assign field_ids automatically:

COPY
    (SELECT 128 AS i)
    TO 'my.parquet'
    (FIELD_IDS 'auto');

Sets the field_id of column i to 42:

COPY
    (SELECT 128 AS i)
    TO 'my.parquet'
    (FIELD_IDS {i: 42});

Sets the field_id of column i to 42, and column j to 43:

COPY
    (SELECT 128 AS i, 256 AS j)
    TO 'my.parquet'
    (FIELD_IDS {i: 42, j: 43});

Sets the field_id of column my_struct to 43, and column i (nested inside my_struct) to 43:

COPY
    (SELECT {i: 128} AS my_struct)
    TO 'my.parquet'
    (FIELD_IDS {my_struct: {__duckdb_field_id: 42, i: 43}});

Sets the field_id of column my_list to 42, and column element (default name of list child) to 43:

COPY
    (SELECT [128, 256] AS my_list)
    TO 'my.parquet'
    (FIELD_IDS {my_list: {__duckdb_field_id: 42, element: 43}});

Sets the field_id of column my_map to 42, and columns key and value (default names of map children) to 43 and 44:

COPY
    (SELECT MAP {'key1' : 128, 'key2': 256} my_map)
    TO 'my.parquet'
    (FIELD_IDS {my_map: {__duckdb_field_id: 42, key: 43, value: 44}});

JSON Options

The below options are applicable when writing JSON files.

Limitations

COPY does not support copying between tables. To copy between tables, use an INSERT statement:

INSERT INTO tbl2
    FROM tbl1;