How to Import a CSV File into PostgreSQL

The fastest reliable way to import a CSV file into PostgreSQL is the COPY command, or psql's \copy when the file is on your own computer rather than the database server. For data you care about, load the file into a staging table first, check the rows, then move the clean ones into the real table in one transaction. A bad export then never half-loads your production data.

This guide covers the commands, the options that fix most broken imports, what to do with bad rows and how to automate a load that repeats. It follows the PostgreSQL 18 documentation, current at the time of writing, and notes where a feature needs a recent version (PostgreSQL COPY documentation).

Choose COPY, \copy or a script

Method	Where the file lives	Permissions needed	Best for
COPY ... FROM '/path/file.csv'	On the database server	Superuser or the pg_read_server_files role, plus INSERT on the table	Loads that run on the server itself
\copy ... FROM 'file.csv' in psql	On the machine running psql	INSERT on the table	One-off imports and hosted databases
COPY ... FROM STDIN from code	Anywhere your program can read it	INSERT on the table	Imports that repeat on a schedule

\copy runs COPY on the server but reads the file on the client and streams it over the connection, so the file only has to exist where psql runs (psql documentation). On hosted databases you usually can't put files on the server, which makes \copy or a script the practical choice.

Load into a staging table first

CREATE UNLOGGED TABLE staging_orders (
  order_id       text,
  customer_email text,
  amount         text,
  ordered_at     text
);

\copy staging_orders FROM 'orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

Every staging column is text on purpose. A malformed date or amount arrives as text you can inspect, instead of stopping the whole load. Unlogged tables skip the write-ahead log, which makes loading faster, but their contents are lost after a crash. That is acceptable for data you can load again. In psql the whole \copy command must stay on one line.

Match the options to the file

• Header row. HEADER true skips the first line. HEADER MATCH also checks that the header names match the table's columns in order and stops with an error if they don't, which catches a reordered export early.
• Delimiter. Spreadsheets in countries that write decimals with a comma often export with semicolons. Use DELIMITER ';'.
• Encoding. If accented characters arrive broken, the file probably isn't UTF-8. Older Windows systems often export in Windows-1252, so try ENCODING 'WIN1252'.
• Empty values. In CSV format an unquoted empty field becomes NULL and a quoted empty string stays an empty string. FORCE_NULL (column) turns quoted empty strings into NULL as well.
• Byte order mark. Spreadsheet exports saved as UTF-8 often start with an invisible byte order mark. COPY treats it as part of the first value, so a header check fails on the first column. Save the file without it or strip it before loading.

Decide what happens to bad rows

By default COPY stops at the first value it can't convert, and none of the rows from that command are kept. PostgreSQL 17 added ON_ERROR ignore, which skips rows whose values fail type conversion and reports how many it skipped. PostgreSQL 18 added REJECT_LIMIT, which still fails the load when too many rows are bad.

\copy orders (order_id, customer_email, amount, ordered_at) FROM 'orders.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 50, LOG_VERBOSITY verbose)

These options only cover conversion errors, such as text in a number column. A duplicate key or a missing required value still stops the command, so the staging approach remains the safer default for data that matters.

Check the rows and move the clean ones

Before moving anything, list the rows that won't convert. pg_input_is_valid, available since PostgreSQL 16, tests a value against a type without raising an error (PostgreSQL validity functions).

SELECT *
FROM staging_orders
WHERE order_id IS NULL
   OR NOT pg_input_is_valid(amount, 'numeric')
   OR NOT pg_input_is_valid(ordered_at, 'timestamptz');

Then move everything that passes in one transaction. ON CONFLICT turns a repeated import into an update instead of a duplicate, as long as the key column has a unique constraint (PostgreSQL INSERT documentation).

BEGIN;

INSERT INTO orders (order_id, customer_email, amount, ordered_at)
SELECT DISTINCT ON (order_id)
       order_id,
       lower(trim(customer_email)),
       amount::numeric(12,2),
       ordered_at::timestamptz
FROM staging_orders
WHERE order_id IS NOT NULL
  AND pg_input_is_valid(amount, 'numeric')
  AND pg_input_is_valid(ordered_at, 'timestamptz')
ORDER BY order_id, ordered_at::timestamptz DESC
ON CONFLICT (order_id) DO UPDATE
SET customer_email = EXCLUDED.customer_email,
    amount         = EXCLUDED.amount,
    ordered_at     = EXCLUDED.ordered_at;

COMMIT;

DISTINCT ON keeps the latest row per key. Without it, a file that lists the same order twice makes the upsert fail, because one statement can't update the same row twice. Rows the filter rejects stay in staging, so you can send the list to whoever owns the export.

Keep large loads fast

• Use COPY instead of individual INSERT statements. PostgreSQL's guidance on populating a database recommends it for bulk loads (PostgreSQL documentation).
• For a first load into an empty table, add indexes and foreign keys after the data is in.
• Run ANALYZE on the table afterwards so the query planner knows about the new rows.
• Keep the move from staging in one transaction, so a failure leaves the real table unchanged.

Automate an import that repeats

When the same export arrives every day or week, script it instead of repeating the steps by hand. With psycopg 3, a Python script can stream the file into COPY FROM STDIN without loading it all into memory (psycopg documentation).

import psycopg

with psycopg.connect(DATABASE_URL) as conn:
    with conn.cursor() as cur:
        cur.execute("TRUNCATE staging_orders")
        with open("orders.csv", encoding="utf-8") as f:
            with cur.copy("COPY staging_orders FROM STDIN (FORMAT csv, HEADER true)") as copy:
                while chunk := f.read(64 * 1024):
                    copy.write(chunk)
        cur.execute(MOVE_CLEAN_ROWS_SQL)
# Leaving the connection block commits, or rolls back if anything failed.

Run it from cron or your job scheduler, record how many rows were loaded and rejected each time, and alert someone when the job fails or the count drops sharply. A file with half its usual rows is usually a broken export, not a quiet day.

When a CSV import is really an integration problem

A file that someone exports from one system and imports into another every week is a manual handoff. It breaks when a column changes, when the person who runs it is away, or when a step is skipped. If the source system has an API, a direct connection can often remove the file entirely. Our guide to connecting business systems without replacing them explains when that is worth doing, and our manual data entry examples show what these handoffs cost. If imports like this take up time every week, that is the kind of workflow our business automation and integration work starts with.