How to Import a CSV using COPY in PostgreSQL

Importing data from CSV files into PostgreSQL is a common and powerful operation, and the COPY command makes it both fast and easy. This tutorial will guide you through the process step by step.

Why Use COPY?

The COPY command is the most efficient way to load large amounts of data into PostgreSQL. Unlike inserting rows one by one, COPY streams data directly into the table from a file or standard input, making it ideal for large datasets.

Basic Syntax

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

✅ FORMAT csv: specifies CSV format
✅ HEADER true: skips the first row if it contains column names

Step 1: Prepare Your CSV File

• Ensure your CSV has a header row that matches your table’s column names (optional but recommended).
• Make sure the file is accessible by the PostgreSQL server (check permissions).

Step 2: Create the Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    signup_date DATE
);

Step 3: Run the COPY Command

COPY users (name, email, signup_date)
FROM '/var/lib/postgresql/users.csv'
WITH (FORMAT csv, HEADER true);

⚠ Note: The file path must be accessible to the PostgreSQL server, not just your local machine.

Step 4: Verify the Import

SELECT * FROM users;

Check that your data appears as expected in the table.

Alternative: Using psql \copy

If you don’t have direct access to the server’s file system, use the \copy command in psql:

\copy users (name, email, signup_date) FROM 'users.csv' WITH (FORMAT csv, HEADER true);

This runs from the client and sends the data to the server.

Best Practices

• Backup your database before importing large files.
• Test the import on a small dataset first.
• Use transactions if you need to roll back on errors.

Troubleshooting

• Permission denied: Check file permissions and PostgreSQL user privileges.
• Invalid input syntax: Make sure data types match the table schema.
• Encoding errors: Specify the encoding using ENCODING 'UTF8' if needed.

Conclusion

With the COPY command, you can import CSV files into PostgreSQL quickly and reliably. Mastering this tool will save you hours of manual data entry and give you a robust way to handle large datasets.

Back to PostgreSQL Tutorials