Exporting data using \COPY in psql

When we were importing data, we used the psql \COPY FROM command to copy data from a file to a table. This time, we'll do it the other way round - from a table to a file - using the \COPY TO command.

\COPY TO can not only copy a full table, but also the results of a SELECT query, and that means we can actually output filtered sub datasets of the source tables.

Similarly to the method we used to import, we can execute \COPY or COPY in different scenarios: We'll use psql in interactive and non-interactive mode, and we'll also do the very same thing in PgAdmin.

Note

It is worth remembering that COPY can only read/write files that can be accessed by an instance of the server, usually files that reside on the same machine as the database server.

For detailed information on \COPY syntax and parameters, type:

\h copy

Exporting data in psql interactively

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

psql -h localhost...