COPY command for bulk data loading in PostgreSQL

Introduction

The COPY command helps you quickly move lots of data into or out of a database table. It is faster than inserting rows one by one.

When you have a large CSV file of customer data to add to your database.

When you want to export a whole table to a file for backup or sharing.

When you need to load data from a spreadsheet into your database.

When you want to quickly copy data between tables or databases.

When importing logs or bulk records from external systems.

Syntax

PostgreSQL

COPY table_name [ ( column_list ) ]
FROM 'file_path'
[ WITH ( option [, ...] ) ];

COPY table_name [ ( column_list ) ]
TO 'file_path'
[ WITH ( option [, ...] ) ];

The COPY command can load data FROM a file into a table or export data TO a file from a table.

Options like FORMAT (csv), DELIMITER, HEADER help control the file format.

Examples

Load data from a CSV file with a header row into the customers table.

PostgreSQL

COPY customers FROM '/tmp/customers.csv' WITH (FORMAT csv, HEADER true);

Export data from the orders table to a CSV file using | as the delimiter.

PostgreSQL

COPY orders TO '/tmp/orders_export.csv' WITH (FORMAT csv, DELIMITER '|');

Load only specific columns from the CSV file into the products table.

PostgreSQL

COPY products (id, name, price) FROM '/tmp/products.csv' WITH (FORMAT csv);

Sample Program

This example creates a simple employees table, loads data from a CSV file with a header, then shows all rows.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
);

COPY employees (name, department) FROM '/tmp/employees.csv' WITH (FORMAT csv, HEADER true);

SELECT * FROM employees;

Important Notes

The file path must be accessible by the PostgreSQL server, not your local machine if using a client.

Use psql client's \copy command to load files from your local machine.

Be careful with file permissions and data formats to avoid errors.

Summary

The COPY command is a fast way to load or export large amounts of data.

It supports CSV and other formats with options for headers and delimiters.

Always check file paths and permissions before running COPY.