PostgreSQL copy command with examples

In this article, we will see PostgreSQL copy command with examples.
PostgreSQL COPY command moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.

COPY FROM syntax:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY TO syntax:

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

PostgreSQL COPY command examples:

1. Copy the data from table to file using PostgreSQL COPY TO command.

COPY (SELECT * FROM payment) TO '/var/lib/pgsql/payments.csv' WITH CSV header;

2. Copy data from a file to PostgreSQL table.

 COPY payments FROM '/var/lib/pgsql/payment24112021.csv' WITH DELIMITER ',' CSV HEADER;

3. Find 5 largest tables in PostgreSQL server database and save the query result to a text or csv file.

COPY (SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC limit 5) TO '/var/lib/pgsql/top5tables.txt';