How to Import a CSV into PostgreSQL using the Command Line

When you need to move large amounts of data, GUI tools can be slow and prone to crashing. Mastering the Postgres import CSV command line workflow is the most efficient way to handle data ingestion. Whether you are working locally or managing a Postgres import CSV to a remote server, using the terminal provides the speed and reliability that developers need.

In this guide, we'll walk through the exact steps to copy CSV to a Postgres table using psql, covering everything from basic setup to cloud-specific workflows. For a broader overview of every available method — including pgAdmin — see our Ultimate Guide to Importing CSV Files into PostgreSQL.

1. Prepare Your Target Table

Before running a SQL query to import data from CSV to Postgres, your target table must already exist. PostgreSQL requires a predefined schema that matches the columns in your file. For a full walkthrough of schema design decisions — column types, null handling, and field splitting — see our guide on Data Mapping 101: How to Connect Your CSV to Postgres.

For a file named data.csv with columns for Name, Age, and Email:

CREATE TABLE users (
    name  VARCHAR(100),
    age   INT,
    email VARCHAR(100)
);

2. Choosing the Right Tool: COPY vs. \copy

One of the main reasons a CSV import fails in Postgres is using the wrong command for the environment. For a deep dive into the performance differences between the two, see our article on PostgreSQL COPY vs. INSERT.

• COPY (Server-Side): The database server reads the file directly from its disk. This requires superuser permissions and is best for local databases.
• \copy (Client-Side): This is a psql meta-command. It reads the file from your local machine and pushes it to the server. This is the gold standard for copy CSV to Postgres psql workflows on remote or managed instances.

3. The Command Line Workflow: Step-by-Step

To load CSV Postgres data effectively, follow these common scenarios.

Case A: Import CSV with a Header Row

If your CSV contains column titles in the first row, use the Postgres import CSV with header syntax:

psql -d my_database -U username -c "\copy users FROM 'path/to/data.csv' WITH (FORMAT csv, HEADER true);"

Case B: Import CSV Without a Header

If the file starts immediately with data, use the Postgres import CSV without header version:

\copy users FROM 'data.csv' WITH (FORMAT csv, HEADER false);

Case C: Importing with Column Mapping

If you need to import CSV into an existing table where the column order differs from your CSV, specify the columns explicitly. This is a key technique covered in our source-to-target mapping guide.

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

4. Importing to the Cloud: AWS RDS & Remote Servers

When working with managed services, you cannot use the server-side COPY command because you don't have access to the underlying file system. To copy CSV to Postgres Amazon RDS, use the \copy command from your local terminal:

psql -h your-rds-endpoint.aws.com -d mydb -U myuser -c "\copy target_table FROM 'local_data.csv' CSV HEADER"

This method ensures a secure, client-side transfer of data to your cloud instance. For teams receiving CSV files from many external customers — each with different column names or encodings — maintaining individual import scripts quickly becomes unmanageable. AI-powered data mapping can automate the schema inference step so every file lands cleanly in your target table without custom scripting.

5. Troubleshooting Common CLI Errors

If your Postgres load CSV attempt fails, check these two common issues. For a complete reference on pre-import checks that prevent data errors, see our guide on advanced data validation strategies for bulk imports.

"Missing Data for Column"

This error usually means your delimiter is wrong. If your CSV uses semicolons (;) instead of commas, you must define it explicitly:

\copy table FROM 'file.csv' WITH (FORMAT csv, DELIMITER ';');

"Permission Denied"

If you are using COPY (without the backslash) and get a permission error, it's because the postgres system user cannot read your personal folder.

• Solution: Switch to the \copy command to use your current user's local permissions.

Summary Checklist

To ensure your Postgres import CSV goes smoothly, remember:

• Match the Schema: Ensure your CREATE TABLE column order matches the CSV, or use explicit column mapping.
• Use Absolute Paths: Always use the full file path to avoid "file not found" errors.
• Define Format: Explicitly state FORMAT csv in your command.
• Check Encodings: Ensure your file is saved in UTF-8 to avoid character errors. Our guide on data normalization covers encoding checks as part of the pre-import workflow.

Further Reading

• The Ultimate Guide to Importing CSV Files into PostgreSQL
• PostgreSQL COPY vs. INSERT: Which Should You Use?
• CSV Bulk Upload Best Practices
• Data Parsing: Extracting Clean Records from CSV