Exporting Data

The COPY INTO command with a file name argument allows for fast dumping of a result set into a (compressed) UTF-8) text file. The file must be accessible by the server and a full path name may be required. The special file STDOUT can be used to direct the result to the primary output channel.

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

The delimiters and NULL AS arguments provide control over the layout of the CSV.

The default field_separator is the pipe '|' character. To change to tab character specify: '\t'. The default record_separator is the newline '\n' character. The default string_quote character is a double quote: ". Character data will be exported in UTF-8 encoding. Other encodings are not supported. The file can also be written directly as compressed file if the file name has extension: .bz2 or .gz or .xz or .lz4. Extension .zip is not recognised, use extension .gz instead.

Examples

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

Let us check 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"