How to import CSV in PostgreSQL

Galaxy Glossary

How do I import a CSV into PostgreSQL?

Use COPY or \copy to bulk-load CSV data directly into a PostgreSQL table.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I import a CSV file into PostgreSQL?<\/h2>Run the server-side `COPY table FROM 'path' WITH (FORMAT csv);<\/code> or the client-side \copy table FROM 'path' WITH (FORMAT csv);<\/code>. Both parse the file and insert rows efficiently.<\/p>`

`When should I choose COPY vs. \copy?<\/h2>`

Use COPY<\/code> when the server can reach the file, e.g., on the same host or mounted storage. Use \copy<\/code> from psql<\/code> when the file resides on your workstation or you lack superuser rights.<\/p>

`What does a minimal command look like?<\/h2>`

\copy sales FROM 'sales.csv' WITH (FORMAT csv, HEADER true);<\/code> reads sales.csv<\/em>, skips the header row, and writes to the sales<\/em> table using the default column order.<\/p>

`How do I map columns or change the delimiter?<\/h2>`

List target columns after the table name and set DELIMITER<\/code>. Example: COPY sales(id, amount) FROM 's.csv' WITH (FORMAT csv, DELIMITER ';');<\/code>.<\/p>

`How can I import only a subset of columns?<\/h2>`

Create a staging table matching the CSV layout, import into it, then INSERT ... SELECT<\/code> the columns you need into the final table. This isolates bad rows and mismatched data types.<\/p>

`What are best practices for large CSV loads?<\/h2>Disable indexes, constraints, and triggers during the load; re-enable them afterward. Increase maintenance_work_mem<\/code>, wrap operations in a transaction, and monitor progress with \watch pg_stat_progress_copy;<\/code>.<\/p>`

Why How to import CSV in PostgreSQL is important

How to import CSV in PostgreSQL Example Usage


\copy customers(id, name, email) FROM '~/downloads/customers.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');

How to import CSV in PostgreSQL Syntax


COPY [schema.]table [(column_list)]
    FROM 'file_path'
    [WITH] (
        FORMAT csv,
        HEADER [true|false],
        DELIMITER 'char',
        NULL 'string',
        QUOTE 'char',
        ESCAPE 'char',
        ENCODING 'name'
    );

\copy has identical options but runs in psql and streams the file from the client.

Common Mistakes

Using COPY without superuser rights. COPY reads server-side files and requires superuser or the pg_read_server_files role. Fix by granting the role or switching to \copy, which runs from psql and needs no extra privileges.
Forgetting WITH (FORMAT csv). Without it, COPY defaults to TEXT and fails on quoted fields or embedded commas. Always specify FORMAT csv (and HEADER if present) to avoid parse errors.

Frequently Asked Questions (FAQs)

Can I import a gzipped CSV directly?<\/h3>No. COPY cannot read compressed files. Decompress first or use `gunzip -c file.gz | psql -c "\copy ... FROM STDIN WITH (FORMAT csv)"<\/code>.<\/p>`

`How do I skip bad rows?<\/h3>`

Import into a staging table with all columns as text, then use INSERT ... SELECT with casts inside TRY ... CATCH-like logic in PL/pgSQL or validate in the client.<\/p>

What permissions are needed for \copy?<\/h3>Only INSERT privileges on the target table and local read access to the CSV file. No superuser role is required.<\/p>

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie