Skip to main content

Exporting Bulk Data

The COPY INTO command with a file name argument allows for fast dumping of a result set into an ASCII file. The file must be accessible by the server and a full path name may be required. The file STDOUT can be used to direct the result to the primary output channel.

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

COPY subquery INTO file_name | STDOUT
            [ [USING] DELIMITERS field_separator
            [',' record_separator [ ',' string_quote ]]]
            [ NULL AS null_string ]

For the output 'file_name', one can specify a file name ending with 'gz', 'bz2' or 'xz' to use the appropriate compression library (if available).

sql>SELECT * FROM cars;
+------+-------+--------+
| y    | make  | model  |
+======+=======+========+
| 2000 | Ford  | Focus  |
| 2001 | Honda | Accord |
| 2004 | Honda | Pilot  |
+------+-------+--------+
3 tuples (1.527ms)
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.csv' DELIMITERS ',','\n';
3 affected rows (2.646ms)
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.gz' DELIMITERS ',','\n';
3 affected rows (3.723ms)
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.bz2' DELIMITERS ',','\n';
3 affected rows (5.729ms)
sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.xz' DELIMITERS ',','\n';
3 affected rows (4.037ms)

Let us check some of the results:

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