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?

Patrick · Accepted Answer · 2015-10-22 00:29:36Z

32

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;

edited Oct 22, 2015 at 0:29

answered Oct 22, 2015 at 0:28

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jonathan Ong Over a year ago

ohhhh. damnit. was hoping it was more automated. thanks

Patrick Over a year ago

Well, this looks as versatile to me as it gets. Simply copy your header line to the COPY command. Easy-peasy in any decent language or even by hand.

Jonathan Ong Over a year ago

oh, smart! how can i do that in bash?

Patrick Over a year ago

uhm, no expert in bash, but read the CSV file up to \n to get your header line, then paste that value into the COPY command. For instance with head -n 1 _filename_.

Craig Ringer Over a year ago

There's lots of room for improvement in the COPY command, and automatic header recognition would be nice. Nobody's working on it as far as I know. Most people who need to do fancier stuff use ETL tools.

|

Chris Hobbs · Accepted Answer · 2019-11-23 00:46:17Z

11

Here's a single line example for importing users using the header row of a csv:

echo "\copy users ($(head -1 users.csv)) FROM 'users.csv' DELIMITER ',' CSV HEADER" | psql

Or with gzip:

echo "\copy users ($(gzip -dc users.csv.gz | head -1)) FROM PROGRAM 'gzip -dc users.csv.gz' DELIMITER ',' CSV HEADER" | psql

answered Nov 23, 2019 at 0:46

Chris Hobbs

5246 silver badges6 bronze badges

1 Comment

étale-cohomology Over a year ago

This answer is just what the doctor ordered

Tomasz Gandor · Accepted Answer · 2019-01-25 12:37:54Z

2

Just to answer Jonathan's comment under the accepted answer - if you want to load the data from CSV "respecting" the column order (I had a few dumps with different schema migration history, or missing columns, which I wanted to import).

If you want to use the CSV headers to import it in Bash: (my table's name is alarms)

#!/bin/bash

if [ -z "$1" ] ; then
    echo "Usage: $0 <alarms_dump_file.csv>"
    exit
fi

columns=$(head -n1 $1)
echo "Using columns:"
if ! echo $columns | grep '^id,' ; then
    echo "Missing id in header. No header present? See below:"
    echo $columns
    exit
fi

sudo -u postgres psql YOUR_DATABASE <<EOF
\copy alarms ( $columns ) FROM '$1' DELIMITER ',' CSV HEADER;
EOF

answered Jan 25, 2019 at 12:37

Tomasz Gandor

8,8632 gold badges63 silver badges58 bronze badges

Collectives™ on Stack Overflow

Postgres CSV COPY from/import is not respecting CSV headers

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related