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.
copy_into_stmt:
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 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"