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.

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.

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 ',' , '\n' , '"';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out3.csv.bz2' DELIMITERS ',','\n';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out2.csv.gz' DELIMITERS ',','\n';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out4.tsv.xz' DELIMITERS '\t','\n';
3 affected rows
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out4.tsv.lz4' DELIMITERS '\t','\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"