AdvancedAdvanced PostgreSQL

COPY command: bulk load CSV data

The query

SQL

-- Import from CSV file
COPY employees(first_name, last_name, email, dept_id, salary, hire_date)
FROM '/path/to/employees.csv'
WITH (
  FORMAT csv,
  HEADER true,
  DELIMITER ',',
  NULL ''
);

-- Export to CSV
COPY (
  SELECT * FROM employees WHERE status = 'active'
) TO '/path/to/export.csv'
WITH (FORMAT csv, HEADER true);

Tested against PostgreSQL 16

Note

“COPY is 10-100x faster than INSERT for bulk loads. Requires server-side file access. Client-side: copy in psql.

Tables referenced

employees10 cols