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