Exporting Data

The COPY INTO command with a file name argument allows for fast export of a result set into a CSV-like text file similar to those read by COPY INTO table FROM file, or to binary files that can be read by COPY BINARY INTO table FROM files.

COPY INTO text file

COPY select_query INTO
    { file_name  [ ON SERVER | ON CLIENT ]
      | STDOUT }
    [ [USING] DELIMITERS field_separator [ ',' record_separator [ ',' string_quote ] ] ]
    [ NULL AS null_string ]

With ON SERVER an absolute path is required, the file must be accessible by the server and it must not already exist. If the file name ends with .bz2, .gz, .xz or .lz4, a compressed file is written. Note that writing .zip files is not supported.
With ON CLIENT the interpretation of the file name depends on the client. In the case of mclient(1), non-absolute paths are interpreted relative to the current working directory of mclient, and compressed files are supported as with ON SERVER. Other clients may have different conventions.
The special destination STDOUT can be used to direct the result to the primary console output channel. The resulting file is always UTF-8 encoded.

The USING DELIMITERS and NULL AS clauses work the same way as the equivalent clauses of the COPY INTO table FROM file statement, though with different defaults: the column separator defaults to the pipe character '|' (same!), the row separator defaults to newline E'\n' (same!) but the quote character defaults to double quotes '"' (different!).

The different defaults means that a file written with the command COPY (SELECT ...) INTO 'file.csv', without a USING DELIMITERS clause, needs a delimiters clause to be read back correctly: COPY INTO mytable FROM 'file.csv' USING DELIMITERS '|', E'\n', '"'; This asymmetry is unfortunate but hard to change due to backward compatibility.

Example

sql>SELECT * FROM cars;
+------+-------+--------+
| yr   | brand | model  |
+======+=======+========+
| 2000 | Ford  | Focus  |
| 2001 | Honda | Accord |
| 2004 | Honda | Pilot  |
+------+-------+--------+
3 tuples
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.csv' USING DELIMITERS ',' , E'\n' , '"';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out3.csv.bz2' DELIMITERS ',',E'\n';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out2.csv.gz' DELIMITERS ',',E'\n';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out4.tsv.xz' DELIMITERS E'\t', E'\n';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out4.tsv.lz4' DELIMITERS E'\t', E'\n';
3 affected rows

Here are some of the results:

$ cat /tmp/cars_out.csv
2000,"Ford","Focus"
2001,"Honda","Accord"
2004,"Honda","Pilot"
$ bzip2 -d cars_out2.csv.bz2 ; cat cars_out2.csv
2000,"Ford","Focus"
2001,"Honda","Accord"
2004,"Honda","Pilot"

COPY INTO BINARY files

COPY select_query INTO
   [ BIG ENDIAN | LITTLE ENDIAN | NATIVE ENDIAN ]
   BINARY file_name [ ',' file_name [ ... ] ]
   [ ON CLIENT | ON SERVER ]

There must be one file name for every column returned by the SELECT query. The files are written in the format described in COPY BINARY INTO table FROM files and the endianness clauses work identically.

When the optional endianess is not specified, NATIVE ENDIAN is used.

When the optional ON CLIENT or ON SERVER is not specified, ON SERVER is used.

When using ON CLIENT, note that older (pre Jun2023) versions of mclient(1) do not support writing binary files and return the error !HY000!unrecognized command from server instead. Upgrade to a more recent version.
With pymonetdb and monetdb-jdbc, all versions that support file transfers also support binary file transfers.