Postgres CSV COPY from/import is not respecting CSV headers

Question

I'm trying to import data from CSV into the table. The issue is that even with CSV HEADER, the CSV is being imported based on the column index, not on the headers of that column.

CREATE TABLE denominations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE churches (
  id SERIAL PRIMARY KEY,
  -- NOT relevant here
  address_id INTEGER REFERENCES addresses,
  denomination_id INTEGER NOT NULL REFERENCES denominations,
  name VARCHAR(100) NOT NULL
);

My CSVs look like:

id,name
1,Southern Baptist Convention
2,Nondenominational
3,Catholic
4,Presbyterian


id,denomination_id,name,address_id
1,1,Saddleback Church,
2,4,First Presbyterian Church,
3,3,St. Elizabeth's Church,
4,3,St Monica Catholic Community,
5,2,Modern Day Saints Church,
6,4,Second Presbyterian Church,

My COPY command looks like this in bash:

psql -d vacation -c "COPY denominations FROM '$PWD/data/Data - Denominations.csv' WITH DELIMITER ',' CSV HEADER;"
psql -d vacation -c "COPY churches FROM '$PWD/data/Data - Churches.csv' WITH DELIMITER ',' CSV HEADER;"

The error I get is:

ERROR:  invalid input syntax for integer: "Saddleback Church"
CONTEXT:  COPY churches, line 2, column denomination_id: "Saddleback Church"

For now, I'm going to rearrange the columns in the CSV, but shouldn't this work?

Answer 1

The COPY command by default copies columns from a CSV file in the default order of the columns in the table. The HEADER option on input is ignored, it basically only informs the backend to ignore the first line on input. If the order of the columns in the CSV does not match the order of the columns in the table, you can explicitly specify the column order to match the layout of the CSV file:

COPY churches (id,denomination_id,name,address_id)
FROM '$PWD/data/Data - Churches.csv'
WITH DELIMITER ',' CSV HEADER;