SQL
`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:  
```sql
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:  
```sql
COPY lineitem TO 'lineitem.tbl' (DELIMITER '|');
```  
Use tab separators to create a TSV file without a header:  
```sql
COPY lineitem TO 'lineitem.tsv' (DELIMITER '\t', HEADER false);
```  
Copy the l_orderkey column of the `lineitem` table to the file `orderkey.tbl`:  
```sql
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:  
```sql
COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.csv' (DELIMITER ',');
```  
Copy the result of a query to the Parquet file `query.parquet`:  
```sql
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`:  
```sql
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`:  
```sql
COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.json' (FORMAT JSON, ARRAY true);
```  
#### `COPY ... TO` Options {#docs:sql:statements:copy::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`.  
| Name | Description | Type | Default |
|:--|:-----|:-|:-|
| `FORMAT` | Specifies the copy function to use. The default is selected from the file extension (e.g., `.parquet` results in a Parquet file being written/read). If the file extension is unknown `CSV` is selected. Vanilla DuckDB provides `CSV`, `PARQUET` and `JSON` but additional copy functions can be added by [`extensions`](#docs:extensions:overview). | `VARCHAR` | `auto` |
| `USE_TMP_FILE` | Whether or not to write to a temporary file first if the original file exists (` target.csv.tmp`). This prevents overwriting an existing file with a broken file in case the writing is cancelled. | `BOOL` | `auto` |
| `OVERWRITE_OR_IGNORE` | Whether or not to allow overwriting files if they already exist. Only has an effect when used with `partition_by`. | `BOOL` | `false` |
| `OVERWRITE` | When set, all existing files inside targeted directories will be removed (not supported on remote filesystems). Only has an effect when used with `partition_by`. | `BOOL` | `false` |
| `APPEND` | When set, in the event a filename pattern is generated that already exists, the path will be regenerated to ensure no existing files are overwritten. Only has an effect when used with `partition_by`. | `BOOL` | `false` |
| `FILENAME_PATTERN` | Set a pattern to use for the filename, can optionally contain `{uuid}` to be filled in with a generated UUID or `{id}` which is replaced by an incrementing index. Only has an effect when used with `partition_by`. | `VARCHAR` | `auto` |
| `FILE_EXTENSION` | Set the file extension that should be assigned to the generated file(s). | `VARCHAR` | `auto` |
| `PER_THREAD_OUTPUT` | Generate one file per thread, rather than one file in total. This allows for faster parallel writing. | `BOOL` | `false` |
| `FILE_SIZE_BYTES` | If this parameter is set, the `COPY` process creates a directory which will contain the exported files. If a file exceeds the set limit (specified as bytes such as `1000` or in human-readable format such as `1k`), the process creates a new file in the directory. This parameter works in combination with `PER_THREAD_OUTPUT`. Note that the size is used as an approximation, and files can be occasionally slightly over the limit. | `VARCHAR` or `BIGINT` | (empty) |
| `PARTITION_BY` | The columns to partition by using a Hive partitioning scheme, see the [partitioned writes section](#docs:data:partitioning:partitioned_writes). | `VARCHAR[]` | (empty) |
| `RETURN_FILES` | Whether or not to include the created filepath(s) (as a `Files VARCHAR[]` column) in the query result. | `BOOL` | `false` |
| `WRITE_PARTITION_COLUMNS` | Whether or not to write partition columns into files. Only has an effect when used with `partition_by`. | `BOOL` | `false` |  
#### Syntax {#docs:sql:statements:copy::syntax}  
<div id="rrdiagram2"></div>