SQL
Format-Specific Options
#### CSV Options {#docs:sql:statements:copy::csv-options}  
The below options are applicable when writing CSV files.  
| Name | Description | Type | Default |
|:--|:-----|:-|:-|
| `COMPRESSION` | The compression type for the file. By default this will be detected automatically from the file extension (e.g., `file.csv.gz` will use gzip, `file.csv` will use `none`). Options are `none`, `gzip`, `zstd`. | `VARCHAR` | `auto` |
| `DATEFORMAT` | Specifies the date format to use when writing dates. See [Date Format](#docs:sql:functions:dateformat) | `VARCHAR` | (empty) |
| `DELIM` or `SEP` | The character that is written to separate columns within each row. | `VARCHAR` | `,` |
| `ESCAPE` | The character that should appear before a character that matches the `quote` value. | `VARCHAR` | `"` |
| `FORCE_QUOTE` | The list of columns to always add quotes to, even if not required. | `VARCHAR[]` | `[]` |
| `HEADER` | Whether or not to write a header for the CSV file. | `BOOL` | `true` |
| `NULLSTR` | The string that is written to represent a `NULL` value. | `VARCHAR` | (empty) |
| `QUOTE` | The quoting character to be used when a data value is quoted. | `VARCHAR` | `"` |
| `TIMESTAMPFORMAT` | Specifies the date format to use when writing timestamps. See [Date Format](#docs:sql:functions:dateformat) | `VARCHAR` | (empty) |  
#### Parquet Options {#docs:sql:statements:copy::parquet-options}  
The below options are applicable when writing Parquet files.  
| Name | Description | Type | Default |
|:--|:-----|:-|:-|
| `COMPRESSION` | The compression format to use (` uncompressed`, `snappy`, `gzip` or `zstd`). | `VARCHAR` | `snappy` |
| `COMPRESSION_LEVEL` | Compression level, set between 1 (lowest compression, fastest) and 22 (highest compression, slowest). Only supported for zstd compression. | `BIGINT` | `3` |
| `FIELD_IDS` | The `field_id` for each column. Pass `auto` to attempt to infer automatically. | `STRUCT` | (empty) |
| `ROW_GROUP_SIZE_BYTES` | The target size of each row group. You can pass either a human-readable string, e.g., `2MB`, or an integer, i.e., the number of bytes. This option is only used when you have issued `SET preserve_insertion_order = false;`, otherwise, it is ignored. | `BIGINT` | `row_group_size * 1024` |
| `ROW_GROUP_SIZE` | The target size, i.e., number of rows, of each row group. | `BIGINT` | 122880 |
| `ROW_GROUPS_PER_FILE` | Create a new Parquet file if the current one has a specified number of row groups. If multiple threads are active, the number of row groups in a file may slightly exceed the specified number of row groups to limit the amount of locking – similarly to the behaviour of `FILE_SIZE_BYTES`. However, if `per_thread_output` is set, only one thread writes to each file, and it becomes accurate again. | `BIGINT` |  (empty) |  
Some examples of `FIELD_IDS` are as follows.  
Assign `field_ids` automatically:  
```sql
COPY
(SELECT 128 AS i)
TO 'my.parquet'
(FIELD_IDS 'auto');
```  
Sets the `field_id` of column `i` to 42:  
```sql
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:  
```sql
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:  
```sql
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:  
```sql
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:  
```sql
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 {#docs:sql:statements:copy::json-options}  
The below options are applicable when writing `JSON` files.  
| Name | Description | Type | Default |
|:--|:-----|:-|:-|
| `ARRAY` | Whether to write a JSON array. If `true`, a JSON array of records is written, if `false`, newline-delimited JSON is written | `BOOL` | `false` |
| `COMPRESSION` | The compression type for the file. By default this will be detected automatically from the file extension (e.g., `file.json.gz` will use gzip, `file.json` will use `none`). Options are `none`, `gzip`, `zstd`. | `VARCHAR` | `auto` |
| `DATEFORMAT` | Specifies the date format to use when writing dates. See [Date Format](#docs:sql:functions:dateformat) | `VARCHAR` | (empty) |
| `TIMESTAMPFORMAT` | Specifies the date format to use when writing timestamps. See [Date Format](#docs:sql:functions:dateformat) | `VARCHAR` | (empty) |