PostgreSQL - copy from / copy to

postgresql-copy_from_copy_to.md

Copy To and Copy From

Copy is a command and a psql metacommand to import data into a PostgreSQL:

-- Tables
\copy test_data.test from sigwx.csv with delimiter '|' csv delimiter E'\t' header quote '"' encoding 'utf-8' null '-'

\copy test_data.test to sigwx.csv with delimiter '|' csv header quote '"' encoding 'utf-8' null '-'

-- With a query
\copy (select a from table) to sig.csv with delimiter '|' csv header quote '"' encoding 'utf-8' null '-'

\copy continental.settlement to '../data/900_out/continental_settlement' with csv header quote '"'

-- In this example the order of the CSV columns is specified in case it does not match the order of columns in
-- the target table. Specify the order of columns in the CSV file and don't forget the csv header modifiers.
\copy continental.settlement3 (settlement_id, name_es, name_en, population, settlement_type_id, geom) from '../data/900_out/continental_settlement' with csv header quote '"'

The seldom used SQL version:

-- A table
copy import.autobuses_lbh_0
from '/home/malkab/Escritorio/Problema_6_23/autobuses_LBH_0.csv'
with delimiter ';'
csv header quote '"';

-- With query
copy (select * from sample_data.vw__export_valid_data)
to '/tmp/export.csv'
with delimiter '|' csv header quote '"'	encoding 'utf-8' null '-';

Please keep in mind that the SQL copy to command operates over the server filesystem, not the client one. Use psql's copy command to use client file system and privileges.

Copy can also copy from the stdin:

copy test (t, id) from stdin with (encoding UTF8, null 'None');
we 5
tu 8
\.

copy sunnsaas.operationlogstatus from stdin with delimiter ',';
0,Not ready
1,Ready
2,Start
3,Ongoing
4,Finished
\.