Using COPY to Load a CSV File Into a PostgreSQL Table
The COPY command in practice: the options that matter, the two ways to feed it, what its row count means, and why one bad value undoes the whole load.
Every guide to loading a CSV into PostgreSQL contains the same one line, and the value is in knowing what it does when something is wrong. COPY is a single SQL command that streams rows into a table in one transaction, with a speed that no loop of INSERT statements approaches. This article covers the form to use, the way the command reports success and failure, and the handful of options that cover almost every file. The full path from an empty database to a verified table is in the guide to loading a CSV into PostgreSQL.
What you need
A table whose columns match the file in order and type, a file, and a role that can insert into the table. The table first:
CREATE TABLE customers (
id int PRIMARY KEY, name text, email text, signup_date date, balance numeric(10,2)
);The file is the one from the hub, with a header line and values that include the awkward cases:
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.75The load
From psql, the command that works on any machine is \copy, which reads the file where psql runs:
psql <<'SQL'
\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)
SELECT * FROM customers ORDER BY id;
SQLCOPY 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)The first line of output, COPY 4, is the command tag, and the number is rows inserted. Check it against the number of data records in your file every time; it is the cheapest verification there is. The table afterwards shows the quoted comma kept inside Smith, Jane, and NULLs, displayed as blanks, for the missing email and balance.
The same command works with the data coming from standard input, which is what \copy is doing underneath and what you use when a program writes the file:
psql -q -c "TRUNCATE customers"
cat customers.csv | psql -c "COPY customers FROM STDIN WITH (FORMAT csv, HEADER true)"COPY 4COPY ... FROM STDIN is a server command that expects the rows to follow on the connection. psql wired the shell pipe into it. This is also the form to use from application code, because every PostgreSQL driver has a way to send data to it, and the difference between \copy and server side COPY is only about where the file lives.
What the options mean
The syntax has a short form (COPY t FROM 'f' CSV HEADER) and a long one in parentheses. I use the long form, because each option is a word you can read. These are the ones I reach for, in rough order of how often.
FORMAT csv selects CSV quoting and escaping and defaults the delimiter to a comma. HEADER true skips the first line, and HEADER match skips it after checking the names, covered in loading a CSV with a header row. NULL 'x' changes the string that means NULL, and FORCE_NULL (col) makes a quoted empty value NULL, both in the NULL article. ENCODING 'LATIN1' declares the file's encoding when it is not the client's, which fixes the error in invalid byte sequences. DELIMITER ';' is for semicolon files, and QUOTE and ESCAPE handle unusual quoting. ON_ERROR ignore and REJECT_LIMIT change the all or nothing rule, and skipping bad rows tests them.
Everything is in the COPY reference, which is one of the better pages in the manual, and the \copy form is described under psql. Two options from it I do not use for CSV: FREEZE, which saves a little work when the table was created or truncated in the same transaction (my measurement showed 2.15 s against 2.29 s, which is not a reason), and FORCE_QUOTE, which applies to COPY TO.
When you forget FORMAT csv
The most common first failure comes from copying a command that lacks FORMAT csv. PostgreSQL then reads its text format, where fields are separated by tabs, and the first line of a comma separated file is one long field:
psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'customers.csv'" 2>&1ERROR: invalid input syntax for type integer: "id,name,email,signup_date,balance"
CONTEXT: COPY customers, line 1, column id: "id,name,email,signup_date,balance"The message names the line (1), the column (id) and the value, which is the entire header. When you see a whole line of your file quoted as the bad value for the first column, the delimiter is wrong. Add FORMAT csv, or DELIMITER ',' if you must use text format.
One bad value, no rows
The second most common failure is a value that does not convert. I loaded a three row file where row 2 has the date 2025-13-45:
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: 0The message says date/time field value out of range, the context names COPY customers, line 3, column signup_date and prints the value, and the last line, from a separate query, shows zero rows in the table. Row 1 was fine and was loaded and then rolled back.
This is the behavior you want for most loads. A file that partly loads is hard to reason about: which rows made it, and was that the same every time? An all or nothing load turns the question into "did the command succeed". The cost is that a 2 GB file with one bad row at the end fails after reading nearly everything, and that is when the options for skipping bad rows and the staging table pattern become useful.
The line number is a physical line of the file, counting the header, and a quoted field with a newline inside it counts as several lines, so the number can be larger than the record number. Open the file at that line, not at the record.
Reading the result
Three habits cover most of the risk. Compare COPY n with the number of records in the file, counted by a CSV aware tool, not wc -l, since quoted newlines make the two differ. Compare a sum of one numeric column against the same sum computed from the file with another tool. And select a few rows containing the awkward cases and read them.
The command tag also tells you about a quiet failure: COPY 0 after a command that read a file means the file was empty or had only a header, and I have seen that exit with status 0 in a script that piped from a failed download. Loading from a URL shows that case and the shell option that exposes it.
What COPY does not do
It does not transform. There is no expression language in the command, so a value must already be in the form the column type accepts, and COPY cannot trim a space, convert a date format, or drop a currency symbol. If the file needs that, load into a text staging table and convert with SQL. It does not merge: COPY inserts, and a duplicate key is an error, not an update. For an upsert, load into a staging table and run INSERT ... ON CONFLICT or MERGE. And it does not select columns from a wider file; its column list names table columns, in file order, as the article on importing some columns explains.
For a file with fewer than a few hundred rows that you load once, a GUI import in your database client is fine, and it uses COPY underneath. For anything repeatable, write the command in a script so the options are recorded. If the table already holds data you want to keep, load into a new table, check it, and swap, so a failure leaves the old one in place.
