How to Load a CSV File Into PostgreSQL Without Losing Rows

Loading a CSV into PostgreSQL is one command, and every way it goes wrong is a message the server prints in plain words. The reasons people lose rows are quieter than errors: a load that "worked" against the wrong column order, a header that was loaded as data, a file cut off at a line boundary, an encoding that mangled names on the way in. This guide follows the path from an empty database to a table you have checked against the file, with measurements from PostgreSQL 18 for the choices that matter and a link to a deeper article for each failure. If you came in with an error message, the table in the middle of the page maps each one to its fix.

The shortest correct load

Four rows, a quoted name with a comma in it and two empty fields. This is the file:

id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,"Smith, Jane",[email protected],2025-02-03,0
3,Bo Chen,,2025-02-20,
4,Cy Dubois,[email protected],2025-03-01,15.75

Create the table with the types you want, then load. \copy is a psql command that reads the file from the machine you run psql on and sends it to the server:

psql <<'SQL'
\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)
SELECT * FROM customers ORDER BY id;
SQL

COPY 4
 id |    name     |       email       | signup_date | balance 
----+-------------+-------------------+-------------+---------
  1 | Ana Silva   | [email protected]  | 2025-01-15  |  120.50
  2 | Smith, Jane | [email protected] | 2025-02-03  |    0.00
  3 | Bo Chen     |                   | 2025-02-20  |        
  4 | Cy Dubois   | [email protected]   | 2025-03-01  |   15.75
(4 rows)

COPY 4 is the server confirming it inserted four rows. The quoted Smith, Jane arrived as one value, the empty email became NULL, and the empty balance became NULL too, because in CSV mode an unquoted empty field is a NULL by default. Using COPY to load a CSV into a table goes through each option, and the article on NULLs and empty strings covers the cases where the default is wrong.

Two things in that command are doing real work. FORMAT csv tells PostgreSQL to use CSV quoting rules, and without it PostgreSQL uses its own tab separated text format and refuses the first line with invalid input syntax for type integer. HEADER true tells it to skip the first line. I prefer HEADER match, which also checks the names, and the header row article shows the failure it prevents.

Why COPY and not INSERT

I loaded the same 2,000,000 rows four ways and timed each three times. The file has an integer id, a customer id, a text ZIP code, a numeric amount, a timestamp, two labels and a note.

python bench_pg.py

method (2,000,000 rows)                       median s     rows/s
COPY into a table with a primary key              3.34    599,654
COPY with no index, key added after               2.72    734,747
COPY into an UNLOGGED table, no index             1.83  1,095,587

method (100,000 rows)                         median s     rows/s
one INSERT per row, one commit                    8.34     11,997
COPY from STDIN through psycopg                   0.08  1,191,230

COPY into a table that already had a primary key took 3.34 s, which is about 600,000 rows per second. Loading with no index and adding the primary key afterwards took 2.72 s in total, and an UNLOGGED table with no index took 1.83 s, because unlogged tables skip the write ahead log. The row by row comparison, run on 100,000 rows to keep it short, is the number to remember: one INSERT per row took 8.34 s, or about 12,000 rows per second, where COPY took 0.08 s for the same rows through the same driver. That is a factor of about 100.

The difference is not magic. Each INSERT is a statement that the server parses, plans and executes, with a round trip between them. COPY is one statement that streams rows in a tight loop. The PostgreSQL manual's page on populating a database recommends it for bulk loads for the same reasons, along with dropping indexes first and raising maintenance_work_mem before rebuilding them.

I also tried the knobs that are often suggested. In three runs each, COPY took 2.29 s as written, 2.15 s with FREEZE, and 2.02 s with synchronous_commit = off. Those differences are inside what I would call noise for a single laptop container, and for a single COPY in one transaction, synchronous_commit has only one commit to skip. The things that moved the number were the index and the logging, and both of those are trade-offs. An UNLOGGED table is empty after a crash and is not replicated, so it suits staging data and nothing you need to keep.

What the load costs in disk and WAL

Speed is one cost and write ahead log volume is another, and the second one decides whether a bulk load disturbs everything else on the server. I measured the WAL written by the same 2,000,000 row load into three kinds of target, along with the size of the finished table:

python wal_and_visibility.py

target table                        WAL written  table + indexes
logged, with primary key                 241 MB           200 MB
logged, no index                         114 MB           157 MB
unlogged, no index                      0 bytes           157 MB

another session, before the COPY commits: 1 row
another session, after the commit      : 100001 rows

Loading into a logged table with a primary key wrote 241 MB of WAL for a 130 MB file, and the finished table with its index took 200 MB. Without the index, the same load wrote 114 MB and the table was 157 MB. An unlogged table wrote none. The pattern is that the table data is logged once, which is about the size of the table, and maintaining the index roughly doubles that, because every index entry is logged too. Building the index after the load is cheaper than maintaining it during, as the earlier timing showed (2.72 s against 3.34 s), and it also writes less in the log, which matters on a server that streams WAL to replicas.

For planning, take 600,000 rows per second as the order of magnitude for a table with a key on a laptop class machine, and scale linearly: 50 million rows of this width is about 83 seconds and a bit over 6 GB of WAL. Your rows are wider, your disk slower and your indexes more numerous, so measure a one million row sample on the real server before promising a window. The WAL figure is the one that surprises people on a replica, which has to replay everything the primary logged.

Loading in parallel

One COPY uses one backend, so one CPU core on the server does the parsing and inserting. If the table has no index, or you add it afterwards, several COPY commands can run at the same time into the same table, each with its own slice of the file. I split the 2,000,000 rows into slices of whole lines with split -n l/N and ran one \copy per slice, checking after each run that the row count was exactly 2,000,000:

python parallel_copy.py

 streams  median s  speed-up
       1      2.12      1.0x
       2      1.17      1.8x
       4      0.65      3.3x
       8      0.46      4.6x

Two streams took 1.17 s against 2.12 s for one, four took 0.65 s and eight took 0.46 s, a 4.6 times speed-up on a 10 CPU container. The gain tapers off, since the single disk and the WAL writer are shared, and past the number of cores there is nothing left to give. Four streams is the point where I would stop on a normal server.

There is one rule that makes this safe or unsafe, which is where you cut the file. split -l and split -n l/N cut between lines, and in a file whose quoted fields contain line breaks, a cut between lines can fall inside a quoted field, and the two halves are both invalid CSV, as the quoting article showed with a three line split. My test file has no embedded newlines, which I checked with the strict record count before splitting. If yours might, split with a CSV aware tool, or run the parallel loads against a staging table filled by a different method. The header must also be stripped from the slices, which is why the script writes the body to a file first and loads with FORMAT csv and no HEADER.

Parallel loading buys the most when the table has no index at all. With a primary key, the streams contend on the index pages and the gain shrinks, so load without it and build the index afterwards, which can itself use parallel workers on a large table.

What COPY does with defaults and triggers

COPY is a bulk path, not a bypass. Constraints, defaults and row level triggers all apply to each row. A column left out of the column list gets its default, and a BEFORE INSERT trigger runs and can change the row. I checked with a table that has a timestamp default, a text default and a trigger that upper cases the name:

psql <<'SQL'
DROP TABLE IF EXISTS tagged;
CREATE TABLE tagged (id int, name text, loaded_at timestamptz DEFAULT now(), source text DEFAULT 'csv');
CREATE FUNCTION upcase_name() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.name := upper(NEW.name); RETURN NEW; END $$;
CREATE TRIGGER t BEFORE INSERT ON tagged FOR EACH ROW EXECUTE FUNCTION upcase_name();
\copy tagged (id, name) FROM STDIN WITH (FORMAT csv)
1,ana
2,bo
\.
SELECT id, name, loaded_at IS NOT NULL AS has_timestamp, source FROM tagged ORDER BY id;
SQL

NOTICE:  table "tagged" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
COPY 2
 id | name | has_timestamp | source 
----+------+---------------+--------
  1 | ANA  | t             | csv
  2 | BO   | t             | csv
(2 rows)

The two rows came in with names upper cased by the trigger, and with loaded_at and source filled by their defaults, although the file had only two columns. That is useful: a loaded_at default is a free audit column, and source can name the file. It also means a slow trigger slows the load by the same factor as it would slow any insert, so a bulk load into a table with heavy triggers is slower than the benchmark above, and the advice is to disable a trigger deliberately in a staging step and not to leave it running by accident.

Foreign keys are checked too. A load into a table that references another will fail on a missing parent, with the usual violates foreign key constraint message, and the whole load rolls back. For large loads into tables with several foreign keys, the common approach is to load into a staging table without them, validate in SQL and insert into the real table.

Readers do not see a half loaded table

Because the load is a single transaction, other sessions see either none of it or all of it. I checked with two connections: one ran a COPY of 100,000 rows into a table that already held one row, and a second counted rows before and after the commit:

another session, before the COPY commits: 1 row
another session, after the commit      : 100001 rows

Before the commit the second session saw one row, the old data. After, it saw 100,001. A reader never sees a partial file, and COPY into an existing table does not block SELECT. It does take a lock that conflicts with schema changes, so an ALTER TABLE waits behind a long load, and the reverse. The TRUNCATE that precedes a reload takes a stronger lock that blocks readers until the transaction ends, which is the reason the script above builds a new table and renames it: the swap holds the lock for milliseconds instead of the length of the load.

Roles and permissions

Run loads as a role that has exactly what the job needs: INSERT on the target, CREATE on the staging schema, and nothing else. \copy needs no special privilege beyond that, since the file is read by psql under your own operating system account. Server side COPY needs pg_read_server_files, and COPY ... PROGRAM needs pg_execute_server_program, which lets the role run any command as the database user; the predefined roles page describes both, and neither belongs on a role that a script or an application uses. A loader that needs superuser to read a file is a loader that should use \copy, or STDIN, from a client that has the file.

\copy or COPY

The two commands read files in different places, and that is the whole difference. \copy is psql reading a file where psql runs and streaming it to the server, so the path is relative to your shell and any user can use it. COPY ... FROM 'path' asks the server process to open a file on the database host, so the path is a server path and the role needs the pg_read_server_files privilege. Without it:

ERROR:  permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
--- the same role with \copy
COPY 4
 count 
-------
     4
(1 row)

If the file is on your laptop and the database is in a container, on RDS or on another host, the server cannot see it, and the error says so and suggests \copy. On my bench, with the database on the same machine, the two took 2.11 s and 2.04 s, so speed is not the reason to choose one. The comparison article has the permission errors, the relative path error and the timings. On managed services, where you do not have a shell on the server, \copy is the answer.

When the load fails

A failed COPY prints the line number and often the column. I counted six messages that account for nearly all the failures I reproduced, and each has an article.

Each error ends the whole load. This is a feature. A bad date on line 3 of a three row file produced this and left zero rows in the table:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'customers-bad-date.csv' WITH (FORMAT csv, HEADER true)" 2>&1
psql -qAt -c "SELECT 'rows in the table afterwards: ' || count(*) FROM customers"

ERROR:  date/time field value out of range: "2025-13-45"
HINT:  Perhaps you need a different "DateStyle" setting.
CONTEXT:  COPY customers, line 3, column signup_date: "2025-13-45"
rows in the table afterwards: 0

The CONTEXT line names the table, the line and the column, and the final line shows the table is untouched. A load is one transaction, and a transaction that fails undoes everything it did. That is exactly why the first row that is wrong makes the headline, and why a 2 GB file that fails at line 1,900,000 costs you the time to read 1,900,000 lines. The tools for that case are skipping bad rows and loading into a staging table first.

Bad rows: skip or stop

PostgreSQL 17 added ON_ERROR ignore, which skips rows whose values cannot be converted and keeps going, and PostgreSQL 18 added REJECT_LIMIT to cap how many it will skip. The PostgreSQL 17 release notes list the first, and the 18 notes the second. On a five row file with two bad values, it loaded three rows and said how many it skipped:

NOTICE:  2 rows were skipped due to data type incompatibility
COPY 3
 id |   name    
----+-----------
  1 | Ana Silva
  4 | Dee Evans
  5 | Eli Fox
(3 rows)

Two limits matter more than the feature. It only skips rows with data type problems, so a row with an extra field still aborts the load, and it tells you which rows it skipped only if you ask for LOG_VERBOSITY verbose. Silent skipping is how a load "succeeds" with 98% of the data, so I use it with a reject limit and I read the notice. The full test is in the article on skipping bad rows.

Clean in SQL, not in the file

When a file is messy, such as dates in three formats, currency symbols and mixed capitalization, the pattern that works is to load everything as text into a staging table and convert with SQL. The conversion is then ordinary SQL you can read and test, and the rows that fail go into a reject table with a reason. PostgreSQL 16 added pg_input_is_valid, which tests whether a string would convert to a type without raising an error, and it makes the reject query straightforward:

f|t|f
                     message                      | detail |                       hint                        | sql_error_code 
--------------------------------------------------+--------+---------------------------------------------------+----------------
 date/time field value out of range: "2025-13-45" |        | Perhaps you need a different "DateStyle" setting. | 22008
(1 row)

The staging table article builds the whole routine on a four row file. The related question of which columns to load has its own article: importing only some CSV columns found that piping the file through qsv select into COPY took 0.8 s for 3 of 8 columns of a 2,000,000 row file, against 3.1 s for loading everything into a staging table and selecting from it.

Compressed and remote files

A gzipped file does not need unpacking. Streaming it through gunzip -c into COPY ... FROM STDIN loaded the 42 MB gzip in 2.13 s, the same as \copy ... FROM PROGRAM, and slightly faster than unpacking to disk first at 2.84 s, because decompression was not the bottleneck. A file at a URL can be streamed straight from curl. The dangers in both are in how failures are reported: a pipeline's exit status is the last command's, so curl -sf url | psql ... on a 404 printed COPY 0 and exited 0. With set -o pipefail it exited 22. The articles on gzipped CSV files and loading from a URL have the commands and the truncation tests.

A load that checks itself

This is the script I would hand to a colleague. It checks the bytes first, counts the records with a strict CSV reader, loads into a new table inside one transaction with HEADER match, compares the loaded count to the file's, and only then swaps the new table in. If anything is wrong, nothing changes.

#!/usr/bin/env bash
# Load orders.csv into PostgreSQL as one transaction, with checks on the way in and on the way out.
set -euo pipefail
file=${1:?usage: load_orders.sh orders.csv}

echo "1. bytes"
python3 preflight_text.py "$file" | grep -v "no findings" && exit 1 || true

expected=$(qsv count "$file")
echo "2. file says $expected records"

psql -v ON_ERROR_STOP=1 -q <<SQL
BEGIN;
DROP TABLE IF EXISTS orders_new;
CREATE TABLE orders_new (
  order_id int PRIMARY KEY, customer_id int NOT NULL, zip text, amount numeric(10,2) NOT NULL,
  created_at timestamp NOT NULL, status text NOT NULL, country char(2), note text);
\copy orders_new FROM '$file' WITH (FORMAT csv, HEADER match, FORCE_NULL (note))
DO \$\$
BEGIN
  IF (SELECT count(*) FROM orders_new) <> $expected THEN
    RAISE EXCEPTION 'loaded % rows, the file has %', (SELECT count(*) FROM orders_new), $expected;
  END IF;
END \$\$;
DROP TABLE IF EXISTS orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;
SQL

echo "3. loaded"
psql -qAt -c "SELECT count(*) || ' rows, amount total ' || sum(amount) FROM orders"

On the 2,000,000 row file it prints the counts and the total:

cp load_orders.sh . && ./load_orders.sh orders-2m.csv

1. bytes
2. file says 2000000 records
NOTICE:  table "orders_new" does not exist, skipping
3. loaded
2000000 rows, amount total 160174646.36

And I check the result against the file with a different tool, comparing a count and a sum, since two independent tools agreeing is the best evidence the load was faithful:

printf 'file : '; duckdb -list -noheader -c "SELECT count(*) || ' rows, amount total ' || round(sum(amount), 2) FROM read_csv('orders-2m.csv')"
printf 'table: '; psql -qAt -c "SELECT count(*) || ' rows, amount total ' || sum(amount) FROM orders"

file : 2000000 rows, amount total 160174646.36
table: 2000000 rows, amount total 160174646.36

Both report 2,000,000 rows and a total of 160174646.36. If a column is lost, truncated or shifted, the sum is the first thing to move. When I corrupted a copy of the file by adding a ninth field to one record, the script stopped before touching the database, and the table kept its previous 2,000,000 rows:

1. bytes
csv error: CSV error: record 49 (line: 50, byte: 2966): found record with 9 fields, but the previous record has 8 fields
exit status 1
orders table still has 2000000 rows

The check came from qsv count, which is strict about the record width, and in a real pipeline I would want the same failure from whichever tool counts. The validation guide lists more checks of this kind, and the encoding guide covers the byte level ones that the preflight in the script calls.

Types to choose before loading

The loader will convert whatever you declare, so the declaration is where mistakes enter. Use text for identifiers that look like numbers, such as ZIP codes, phone numbers and account numbers, because integer drops leading zeros and an integer ZIP code of 501 is not 00501, as the types article measured on this same file. Use numeric(10,2) for money and not real or double precision, which store binary approximations. Use timestamp and not timestamptz only when the source has no time zone and you intend to treat it as local time, and use date for dates, with the string in ISO form. Use char(2) or text with a check constraint for codes; I used char(2) for the country and it padded nothing because all values had two letters.

Do not guess the widths and ranges; ask the file. qsv stats reads the whole file once and reports, per column, the type it inferred, the smallest and largest values, the shortest and longest lengths and the count of empty cells. This is its output for the 2,000,000 row file:

qsv stats --everything orders-2m.csv | qsv select field,type,min,max,min_length,max_length,nullcount | qsv table

field        type     min                  max                  min_length  max_length  nullcount
order_id     Integer  1                    2000000                                      0
customer_id  Integer  1                    249999                                       0
zip          String   00500                99998                5           5           0
amount       Float    0.04                 654.24                                       0
created_at   String   2025-01-01 00:00:04  2025-12-31 23:59:38  19          19          0
status       String   failed               shipped              4           8           0
country      String   BR                   US                   2           2           0
note         String   call first           rush order           0           13          400697

Read it as a checklist. order_id runs from 1 to 2,000,000 and customer_id stops at 249,999, so integer is right for both and smallint is not. zip is a string of exactly five characters with a minimum of 00500, which settles that it must be text and that the leading zeros are real. amount has a maximum of 654.24, so numeric(10,2) has room to spare. created_at is a string of length 19 everywhere, a fixed width timestamp that PostgreSQL's timestamp accepts directly. status is at most 8 characters, country is exactly 2, and note is empty in 400,697 rows, which is the cell to think about: an unquoted empty becomes NULL in CSV mode, so 400,697 notes will be NULL, and if you wanted empty strings you need FORCE_NOT_NULL.

That table takes a few seconds to produce and prevents the two bugs I trust least in a hurry: a column declared too narrow, which fails the load at the first long value and costs a rerun, and a column declared as a number when it holds an identifier. If min_length and max_length differ for a number column, some values have leading zeros or spaces and it is probably not a number.

Constraints are worth adding at load time. A NOT NULL on a column the file should always fill, a primary key on the id, and a CHECK on a status list turn a quiet problem into a failed load, which is what you want. They also slow the load a little, as the primary key row of the benchmark shows, and I accept the 0.6 s.

Where each article fits

For a first load, start with using COPY and the header row article. For file location and permissions, see \copy against server COPY. For the failures, see extra data after the last column and invalid byte sequences. For selecting columns and cleaning, see importing only some columns and staging tables. For NULLs, see empty strings and NULL. For rejects, see skipping bad rows. For delivery, see gzipped files and URLs. And when the same problem is MySQL or SQLite instead, the MySQL import guide and the SQLite import guide are the equivalents.

When COPY is not the right tool

COPY loads a file that is complete and well formed into a table you control. Four situations call for something else.

If the data arrives continuously, a few rows at a time, you are not bulk loading, and INSERT through a connection pool, or a queue in front of batched COPY through a client library, is the pattern. The 100,000 row result above is the penalty for doing it one row at a time, so batch.

If you do not have the right to create tables or run COPY, as on some managed or locked down databases, ask for a staging schema or load through the provider's import service. The client side \copy works for any role with INSERT permission, and that covers most cases.

If the file is more than a few times larger than the disk you have, or you need to query it once and throw it away, loading is the wrong step. DuckDB can query a CSV in place, and PostgreSQL's file_fdw extension can expose a file on the server as a foreign table, though a table scan over text is slow.

If the data is not one table, a spreadsheet with several sheets, an XML feed or nested JSON, a CSV export loses structure that the source had. Convert from the original format, not through CSV.