Loading a CSV With a Header Row Into PostgreSQL Tables

A CSV header is a promise about column order, and the quiet way to break it is to let the load skip the header without checking. The data then goes into whatever column is in that position, and if the types happen to fit, as they do for text columns, nobody notices until a report shows an email address in a name field. PostgreSQL has the check built in, and this article shows what each header option does, the four errors you can get, and the way to load a file whose columns come in a different order than the table's.

What you need

A table, a file with a header line, and a sentence in your head about whether the order of columns in the file is the same as in the table. The table is customers (id, name, email, signup_date, balance) and the baseline file starts id,name,email,signup_date,balance.

What HEADER true does

HEADER true tells COPY that the first line is a header and should be skipped. It does not look at the content:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)"
psql -qAt -c "SELECT id FROM customers ORDER BY id" | tr '\n' ' '; echo

COPY 4
1 2 3 4 

Four rows loaded, ids 1 to 4. Any first line would have been skipped as well, so a file whose first line is a note or a blank line would silently lose a data row or fail oddly. The PostgreSQL 15 release notes added a stricter value that verifies the names, and the COPY reference lists HEADER as a boolean or match.

What HEADER match does

HEADER match reads the first line, splits it by the delimiter and compares each name with the corresponding column of the table, or of the column list you gave. If the number or the names differ, it stops before inserting a row. I tried the four ways a file can disagree with the table.

A different column order. The export had email before name:

psql -c "\copy customers FROM 'header-reordered.csv' WITH (FORMAT csv, HEADER match)" 2>&1

ERROR:  column name mismatch in header line field 2: got "email", expected "name"
CONTEXT:  COPY customers, line 1: "id,email,name,signup_date,balance"

The message names the field number and both names: column name mismatch in header line field 2: got "email", expected "name". If the types had fit, a plain HEADER true load would have put emails in the name column. That is the failure HEADER match exists to prevent, and it is the one I most often see in production, because someone adds or reorders a column in an export.

A different case. The header was ID,Name,EMAIL,Signup_Date,Balance:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'header-case.csv' WITH (FORMAT csv, HEADER match)" 2>&1

ERROR:  column name mismatch in header line field 1: got "ID", expected "id"
CONTEXT:  COPY customers, line 1: "ID,Name,EMAIL,Signup_Date,Balance"

The match is case sensitive, so ID is not id. If the sender's tool capitalizes headers and you cannot change it, either name your columns in the same case with double quotes, which is a bad idea, or lower the header in a pre-processing step, or use a column list with HEADER true and accept the lost check.

An extra column. A new field source at the end:

psql -c "\copy customers FROM 'header-extra.csv' WITH (FORMAT csv, HEADER match)" 2>&1

ERROR:  wrong number of fields in header line: got 6, expected 5
CONTEXT:  COPY customers, line 1: "id,name,email,signup_date,balance,source"

wrong number of fields in header line: got 6, expected 5. That is the cheapest warning a pipeline can get that the sender changed the export, and it arrives before any data moves.

What happens with no header option

If you forget it, the header is data. The first column of line one is the text id, and the integer column refuses it:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'customers.csv' WITH (FORMAT csv)" 2>&1

ERROR:  invalid input syntax for type integer: "id"
CONTEXT:  COPY customers, line 1, column id: "id"

The error shows column id: "id", with the line number 1. When the offending value is the name of a column, the header was loaded as data. With all text columns it would have succeeded, which is why I declare real types where I can, and why a header row inside a table of text columns is one of the common quiet bugs.

Loading when the order differs

If the file's columns are in a different order than the table's, you do not have to change either. Name the table's columns in the order the file has them. COPY maps the first field of each line to the first listed column, and so on:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers (id, email, name, signup_date, balance) FROM 'header-reordered.csv' WITH (FORMAT csv, HEADER match)" -c "SELECT id, name, email FROM customers" 2>&1
psql -c "\copy customers (id, name, email, signup_date, balance) FROM 'header-reordered.csv' WITH (FORMAT csv, HEADER match)" 2>&1

COPY 1
 id | name |     email      
----+------+----------------
  9 | Zed  | [email protected]
(1 row)

ERROR:  column name mismatch in header line field 2: got "email", expected "name"
CONTEXT:  COPY customers, line 1: "id,email,name,signup_date,balance"

The reordered file, with email before name, loaded with the column list (id, email, name, signup_date, balance) and the row came out right: name Zed, email [email protected]. The second command used the table's own order and failed with the same mismatch error as before. So with a column list, HEADER match checks the file's header against the list, not against the table definition. The combination of a column list that follows the file and HEADER match gives you both flexibility and a check: the load succeeds when the file's columns are exactly the ones you named, in that order, and fails otherwise. It is not a way to load by header name regardless of position. PostgreSQL has no such feature; matching by name needs a staging table or another tool.

Names with spaces, and other awkward headers

HEADER match compares the header text with the column name as PostgreSQL stores it. A header of signup date with a space, or Order-ID, will not match a column unless the column was created with that exact quoted name, which makes every later query awkward. The practical routes are to let the file's header drive the table definition, with the awkward names cleaned in one place, or to load with HEADER true and a column list, and to check the header yourself in the script. I normally do the second: the script reads the first line, lowercases and replaces spaces with underscores, compares it with the expected list and aborts on a difference. That is the same check with a rule I control, and it fits in five lines of shell or Python.

Duplicate names are another trap, covered in the header row article, since COPY does not care and a table cannot have two columns of the same name. And a header with a byte order mark in front of the first name fails HEADER match, with an error that looks like the names are identical, which they are not, as the BOM article shows.

How to tell it worked

Three checks cover it. The command tag COPY n must equal the number of data records, not counting the header. A SELECT of the first and last rows should show the expected first and last ids. And a query for the properties the header guarantees, such as SELECT count(*) FROM customers WHERE email NOT LIKE '%@%', should return zero if the email column holds emails. That last check is where a shifted column shows itself if you used HEADER true, and with HEADER match it is a second line of defense.

When the header is not the first line

HEADER assumes the header is line one. Reports exported from business software often begin with a title, a date range or a blank line. COPY has no skip option, so the choices are to strip the lines before loading, with tail -n +4 report.csv | psql -c "COPY ... FROM STDIN CSV HEADER", or to load everything as text into a staging table and filter in SQL, as in the staging table article. Files that repeat the header every few thousand rows, which happens when files were concatenated, load their repeated headers as data rows and fail on the types. Remove those with a filter, or load into text and delete rows where id = 'id'.

And if the file has no header at all, use HEADER false, the default, and give a column list if the order is not the table's. That is the case where a written contract matters most, since nothing in the file can tell you the order, as the format guide explains.