How to Import CSV Files into PostgreSQL Quickly and Easily

To import a CSV file into PostgreSQL, use the COPY command with the file path and specify CSV format. For example, COPY tablename FROM '/path/to/file.csv' CSV HEADER; imports the CSV data including the header row.

Syntax

The basic syntax to import a CSV file into PostgreSQL is:

• COPY tablename FROM 'filepath' CSV HEADER;
• tablename is the target table in your database.
• filepath is the full path to your CSV file on the server.
• CSV tells PostgreSQL the file format.
• HEADER means the first row contains column names and will be skipped.

sql

COPY tablename FROM '/full/path/to/file.csv' CSV HEADER;

Example

This example shows how to import a CSV file named employees.csv into a table called employees. The CSV file has a header row with column names.

sql

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

COPY employees(name, department, salary) FROM '/tmp/employees.csv' CSV HEADER;

Output

COPY 3

Common Pitfalls

Common mistakes when importing CSV files include:

• Using a file path that the PostgreSQL server cannot access. The file must be on the server machine.
• Not specifying HEADER when the CSV has column names, causing errors or wrong data import.
• Mismatch between CSV columns and table columns in order or count.
• Incorrect file permissions preventing PostgreSQL from reading the file.

To avoid these, ensure the file is accessible, use HEADER if needed, and match columns carefully.

sql

/* Wrong: Missing HEADER when CSV has column names */
COPY employees FROM '/tmp/employees.csv' CSV;

/* Right: Include HEADER to skip first row */
COPY employees FROM '/tmp/employees.csv' CSV HEADER;

Quick Reference

Command	Description
COPY tablename FROM 'filepath' CSV;	Import CSV without header row
COPY tablename FROM 'filepath' CSV HEADER;	Import CSV with header row
\copy tablename FROM 'filepath' CSV HEADER;	Client-side import using psql tool
COPY tablename TO 'filepath' CSV HEADER;	Export table data to CSV file

Key Takeaways

Use the COPY command with CSV and HEADER options to import CSV files correctly.

The CSV file must be accessible by the PostgreSQL server with proper permissions.

Match CSV columns with table columns to avoid import errors.

Use \copy in psql for client-side CSV imports if you don't have server file access.