psql - PostgreSQL: Up and Running - page 53

\copy sometable FROM somefile.txt DELIMITER '|';
If you want to replace null values with something else, add a NULL AS :
\copy sometable FROM somefile.txt NULL As '';
Don't confuse the \copy command in psql with the COPY statement
provided by the SQL language. Because psql is a client utility, all paths
are interpreted relative to the connected client. The SQL copy is
server-based and runs under the context of the postgres service OS
account. The input file must reside in a path accessible by the post‐
gres service account. We detail the differences between the two in
Import Fixed-width Data in PostgreSQL with psql .
psql Export
Exporting data is even easier than importing data. You can even export selected rows
from a table. Use the psql \copy command to export. In Example 3-8 , we demonstrate
how to export the data we just loaded back to a tab-delimited file.
Example 3-8. Exporting data with psql
\connect postgresql_book
\copy (SELECT * FROM staging.factfinder_import WHERE s01 ~ E'^[0-9]+' ) TO '/
test.tab'
WITH DELIMITER E'\t' CSV HEADER
The default behavior of exporting data without qualifications is to export to a
tab-delimited file. However, the tab-delimited format does not export header columns.
You can use the HEADER option only with the CSV format (see Example 3-9 ).
Example 3-9. Exporting data with psql
\connect postgresql_book
\copy staging.factfinder_import TO '/test.csv' WITH CSV HEADER QUOTE '"' FORCE
QUOTE *
FORCE QUOTE * ensures that all columns are double quoted. For clarity, we also indicate
the quoting character even though psql assumes double quotes if quotes are omitted.
Copy from/to Program
Since PostgreSQL 9.3, psql can fetch data from the output of command-line programs
—such as curl , ls , and wget —and dump the data into a table. Example 3-10 imports a
directory listing from a dir command.