In order to export the data in interactive mode, we first need to connect to the database using psql:

psql -h localhost -p 5434 -U postgres

Then type the following:

\c mastering_postgis

Once connected, we can execute a simple command:

\copy data_import.earthquakes_csv TO earthquakes.csv WITH DELIMITER ';' CSV HEADER

The preceding command exported a data_import. earthquakes_csv table to a file named earthquakes.csv, with ';' as a column separator. A header in the form of column names has also been added to the beginning of the file. The output should be similar to the following:

COPY 50

Basically, the database told us how many records have been exported. The content of the exported file should exactly resemble the content of the table we exported from:

time;latitude;longitude;depth;mag;magtype;nst;gap;dmin;rms;net;id;updated;place;type;horizontalerror;deptherror;magerror;magnst;status;locationsource;magsource
2016-10-08 14:08:08.71+02;36.3902;-96.9601;5;2.9;mb_lg;;60;0.029;0.52;us;us20007csd;2016-10-08 14:27:58.372+02;15km WNW of Pawnee, Oklahoma;earthquake;1.3;1.9;0.1;26;reviewed;us;us

As mentioned, \COPY can also output the results of a SELECT query. This means we can tailor the output to very specific needs, as required. In the next example, we'll export data from a spatialized earthquakes table, but the geometry will be converted to a WKT (well-known text) representation. We'll also export only a subset of columns:

\copy (select id, ST_AsText(geom) FROM  data_import.earthquakes_subset_with_geom) TO earthquakes_subset.csv WITH CSV DELIMITER '|' FORCE QUOTE *  HEADER

Once again, the output just specifies the amount of records exported:

COPY 50

The executed command exported only the id column and a WKT-encoded geometry column. The export force wrapped the data into quote symbols, with a pipe (|) symbol used as a delimiter. The file has header:

id|st_astext
"us20007csd"|"POINT(-96.9601 36.3902)"
"us20007csa"|"POINT(-98.7058 36.4314)"