Working with Import and Export

COPY orders TO '/var/lib/postgresql/exports/orders.csv'
   WITH (FORMAT csv, HEADER true);

COPY orders FROM '/var/lib/postgresql/imports/orders.csv'
   WITH (FORMAT csv, HEADER true);

COPY t FROM '/tmp/in.tsv' WITH (FORMAT text, DELIMITER E'\t');

COPY t TO '/tmp/out.csv' WITH (FORMAT csv, HEADER true, QUOTE '"', ESCAPE '"');

COPY t FROM '/tmp/in.csv' WITH (FORMAT csv, HEADER match);  -- PG 16+: validate header

COPY t TO '/tmp/out.csv' WITH (FORMAT csv, NULL '\N');
COPY t FROM '/tmp/in.csv' WITH (FORMAT csv, NULL '');

COPY t TO STDOUT WITH (FORMAT csv, QUOTE '''', FORCE_QUOTE *);

COPY t FROM '/tmp/in.csv' WITH (FORMAT csv, ESCAPE '\');

psql -c "\copy orders TO 'orders.csv' WITH (FORMAT csv, HEADER true)"

COPY t FROM PROGRAM 'gunzip -c /imports/big.csv.gz' WITH (FORMAT csv, HEADER true);
COPY t TO   PROGRAM 'gzip -9 > /exports/big.csv.gz' WITH (FORMAT csv, HEADER true);

CREATE TEMP TABLE raw (j jsonb);
\copy raw FROM 'events.jsonl' (FORMAT csv, QUOTE E'\b', DELIMITER E'\b');
INSERT INTO events SELECT (j->>'id')::int, j->>'name', j FROM raw;

COPY (SELECT id, total_cents FROM orders WHERE status='paid')
  TO '/tmp/paid.csv' WITH (FORMAT csv, HEADER true);

psql -c "COPY t TO STDOUT WITH (FORMAT csv, HEADER true)" > out.csv
cat in.csv | psql -c "COPY t FROM STDIN WITH (FORMAT csv, HEADER true)"