Skip to content
DBAExperts
PostgreSQL Migration & data

How to export and import data in PostgreSQL with COPY

COPY is the fastest way to move data in bulk between PostgreSQL and CSV files. It is much faster than inserting row by row with INSERT.

  1. 1

    Export a table to CSV from the server

    COPY TO writes the file on the server's filesystem and requires superuser permissions or the pg_write_server_files role.

    COPY clientes TO '/tmp/clientes.csv' WITH (FORMAT csv, HEADER true);
  2. 2

    Export from the client with \copy

    If you do not have access to the server's disk, psql's \copy writes the file on your local machine using your system permissions.

    \copy clientes TO 'clientes.csv' WITH (FORMAT csv, HEADER true)
  3. 3

    Import data from a CSV

    COPY FROM loads the file into the table. The table must exist and the columns must match the CSV order, or specify them explicitly.

    \copy clientes FROM 'clientes.csv' WITH (FORMAT csv, HEADER true)
  4. 4

    Speed up large loads by dropping indexes temporarily

    For large initial loads it is better to load first and create the indexes afterward: keeping them during the COPY makes the load much slower.

    -- 1) carga con \copy
-- 2) luego:
CREATE INDEX idx_clientes_email ON clientes (email);

// common mistake

Confusing COPY (runs on the server, needs superuser and server-side paths) with \copy (runs in the psql client, uses local paths). Many permission-denied errors are solved by switching COPY for \copy.

// when it's worth an expert

When the import is part of a migration with data transformation, mismatched types, or millions of rows, the process gets complicated fast. At dba.mx we plan the load with validation and rollback so the data arrives intact, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.