Importing Only Some CSV Columns Into a PostgreSQL Table

The export has eight columns, the table you want has three, and COPY will not skip the five in between. Its column list names the columns of the table, in the order the file supplies them, so there is no way to say "take fields 1, 4 and 7". People often discover this by getting the extra data after last expected column error on their first try. There are two good answers and one bad one, and this article times the good ones on a 2,000,000 row file. If the error you are looking at is the other one, with a table that matches the file, see the extra data article.

What you need

The wide file, a narrow table, and a CSV aware tool to select columns. The file starts like this:

head -3 orders-2m.csv

order_id,customer_id,zip,amount,created_at,status,country,note
1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order
2,193489,34494,31.59,2025-03-03 08:03:33,failed,BR,leave at door

The table keeps three columns:

CREATE TABLE slim (order_id int, amount numeric(10,2), country char(2));

Why a column list does not do it

The column list in COPY is the list of table columns the incoming fields are assigned to. Giving it three names tells PostgreSQL to expect three fields on each line, and the file has eight:

psql -c "\copy slim (order_id, amount, country) FROM 'orders-2m.csv' WITH (FORMAT csv, HEADER true)" 2>&1 | head -3

ERROR:  extra data after last expected column
CONTEXT:  COPY slim, line 2: "1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order"

The error shows the whole first record and complains about extra data, because after three fields there are five more. The COPY reference describes the column list as the columns of the table to be copied, which is the other direction from what you wanted. So the unwanted columns have to be removed before COPY sees the rows, or loaded and ignored.

Option 1: filter first, with a CSV aware tool

Pipe the file through a tool that selects columns by name and pass the result to COPY ... FROM STDIN. With qsv, the selection is qsv select order_id,amount,country, and the header line comes out with the three names, so HEADER true still works:

psql -q -c "TRUNCATE slim"
time (qsv select order_id,amount,country orders-2m.csv | psql -c "COPY slim FROM STDIN WITH (FORMAT csv, HEADER true)")
psql -qAt -c "SELECT count(*) FROM slim"

COPY 2000000

real	0m0.817s
user	0m0.292s
sys	0m0.153s
2000000

COPY 2000000, in 0.82 seconds of wall time for the whole pipeline, with a spot check count of 2,000,000 afterwards. The CPU time was 0.3 s, since the tools ran in parallel and each stream was cheap. The same selection works with mlr, csvcut -c from csvkit, or duckdb -c "COPY (SELECT ...) TO STDOUT".

If you prefer one command to a shell pipe, \copy ... FROM PROGRAM runs the filter on the client and reads its output:

psql -q -c "TRUNCATE slim"
psql -c "\copy slim FROM PROGRAM 'qsv select order_id,amount,country orders-2m.csv' WITH (FORMAT csv, HEADER true)"

COPY 2000000

Same result, and a single line in a script. The program runs on the machine where psql runs, which is a reason to prefer \copy to server COPY ... FROM PROGRAM, which would run it on the database server and needs the pg_execute_server_program role, as the roles reference explains.

Option 2: load everything into a staging table

The other route is to load all eight columns into an unlogged staging table and select the three you want:

psql <<'SQL'
\timing on
DROP TABLE IF EXISTS stage; CREATE UNLOGGED TABLE stage (order_id int, customer_id int, zip text, amount numeric(10,2), created_at timestamp, status text, country char(2), note text);
\copy stage FROM 'orders-2m.csv' WITH (FORMAT csv, HEADER true)
INSERT INTO slim SELECT order_id, amount, country FROM stage;
SELECT count(*) FROM slim;
SQL

Timing is on.
NOTICE:  table "stage" does not exist, skipping
DROP TABLE
Time: 0.229 ms
CREATE TABLE
Time: 1.747 ms
COPY 2000000
Time: 1943.109 ms (00:01.943)
INSERT 0 2000000
Time: 1199.965 ms (00:01.200)
  count  
---------
 2000000
(1 row)

Time: 41.507 ms

The COPY took 1.94 s and the INSERT ... SELECT another 1.20 s, 3.1 s together against 0.82 s for the pipe. The staging route needs disk for all eight columns twice, once in the staging table and once in the target. In exchange you have the unwanted columns in SQL, where you can inspect, filter or clean them before deciding, and where one load can feed several narrow tables. If you might want a column later, or you are not sure which ones matter, staging is the better start, and the staging table article shows the pattern with conversions.

I would not take this table as a ranking of the methods. The pipe won because it avoided writing five columns that I did not need, and it would have won by less on a file where I needed six of eight. What is true generally is that doing less work before the data reaches PostgreSQL is cheaper than doing it afterward.

Why not cut or awk

The standard tools of the shell, cut -d, -f1,3 and awk -F, '{print $1","$3}', split on every comma and know nothing about quotes. On this small file with a quoted comma in the name:

printf 'id,name,city\n1,"Smith, Jane",Lyon\n' > /tmp/q.csv
echo "cut:"; cut -d, -f1,3 /tmp/q.csv
echo "qsv:"; qsv select id,city /tmp/q.csv

cut:
id,city
1, Jane"
qsv:
id,city
1,Lyon

cut returned 1, Jane" for the record 1,"Smith, Jane",Lyon: the third field by its count is the second half of the name, and the city is gone. qsv select id,city returned 1,Lyon. Nothing in the cut output says anything is wrong, and the load would succeed. If your file has no quoted fields and you have verified it with a CSV parser, cut is fast and fine. The quoting article explains why it cannot be right in general.

Rename and reorder at the same time

The filter also solves the order problem. qsv select country,order_id,amount reorders the output, so the file matches the table without a column list. For renaming, qsv rename or mlr rename changes the header, which matters if you are using HEADER match, since the match compares names. A selection with a rename and HEADER match gives you a file whose header is exactly the table's and a load that will fail if anything upstream changes shape, which is how I like to run it in a pipeline.

How to tell it worked

The COPY n tag must equal the data records in the source: qsv count gives 2,000,000. A sum of the loaded numeric column should match a sum over the source column computed by a different tool, such as DuckDB's read_csv. And check one record by id against the source with the quoted comma case, since that is where a bad selector shows itself.

When you need every column anyway

If the table should hold the whole record, there is nothing to filter, and the only decision is how to represent the columns. If you are unsure whether you need the rest, load everything. Dropping five columns later with ALTER TABLE ... DROP COLUMN is cheap, since PostgreSQL marks them dropped without rewriting the table, though the space is not returned until a rewrite.

And if the file is so large that even reading it hurts, consider not loading it at all. DuckDB can select three columns of a CSV and write only those, or query the file in place, as described in querying CSV files with DuckDB, and Parquet files can be read column by column without scanning the rest, which is the real answer to "I only need three columns" at scale.