How to Use COPY Command in PostgreSQL: Syntax and Examples

In PostgreSQL, the COPY command is used to transfer data between a table and a file. You can use COPY to import data from a file into a table or export data from a table to a file quickly and efficiently.

Syntax

The COPY command has two main forms: copying data to a file and copying data from a file. You specify the table name, the direction (TO or FROM), and the file path. Optional parameters include the data format (like CSV), delimiter, header presence, and null string representation.

• COPY table_name TO 'file_path' [WITH options]; exports data from the table to a file.
• COPY table_name FROM 'file_path' [WITH options]; imports data from a file into the table.

sql

COPY table_name TO '/path/to/file.csv' WITH (FORMAT csv, HEADER true);
COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true);

Example

This example shows how to export data from a table called employees to a CSV file, then import it back into another table called employees_backup. It demonstrates using the CSV format with a header row.

sql

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

INSERT INTO employees (name, department) VALUES
('Alice', 'HR'),
('Bob', 'IT'),
('Carol', 'Finance');

-- Export data to CSV file
COPY employees TO '/tmp/employees.csv' WITH (FORMAT csv, HEADER true);

-- Create backup table
CREATE TABLE employees_backup (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT
);

-- Import data from CSV file
COPY employees_backup (id, name, department) FROM '/tmp/employees.csv' WITH (FORMAT csv, HEADER true);

Output

COPY 3
COPY 3

Common Pitfalls

Common mistakes when using COPY include:

• Using file paths that the PostgreSQL server cannot access due to permissions or wrong path.
• Confusing client-side and server-side file paths. The COPY command reads/writes files on the database server, not the client machine.
• Not matching the file format and delimiter with the actual file content.
• Forgetting to specify columns when the file has a different column order or subset.

To avoid these, use \copy in psql for client-side file operations or ensure server file access and correct options.

sql

/* Wrong: server cannot access client file path */
COPY employees FROM '/home/user/employees.csv' WITH (FORMAT csv);

/* Right: use psql client command for client files */
\copy employees FROM '/home/user/employees.csv' WITH (FORMAT csv);

Quick Reference

Option	Description	Example
FORMAT	Specifies file format (text, csv, binary)	FORMAT csv
DELIMITER	Character separating columns	DELIMITER ','
HEADER	Indicates if file has header row	HEADER true
NULL	String representing NULL values	NULL '\\N'
QUOTE	Character used for quoting in CSV	QUOTE '"'

Key Takeaways

Use COPY to efficiently import or export table data to files on the server.

Specify file format and options like FORMAT csv and HEADER true to match your file.

Remember COPY accesses server files; use \copy in psql for client files.

Check file paths and permissions to avoid access errors.

Always match columns and data format between the file and the table.