Postgres CSV复制/导入未遵守CSV标题

Question

我正在尝试将CSV数据导入表中。问题在于，即使使用了CSV HEADER，CSV仍然是根据列索引而不是该列的标题进行导入的。

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
);

我的CSV文件看起来像：

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,

我的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;"

我得到的错误是：

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

目前，我将重新排列CSV中的列，但这样行得通吗？

- Jonathan Ong

Answer 1

COPY命令默认按照表格中列的默认顺序从CSV文件中复制列。 HEADER选项on input is ignored，它基本上只是通知后端忽略输入的第一行。如果CSV文件中列的顺序与表格中列的顺序不匹配，则可以明确指定列顺序以匹配CSV文件的布局：

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

- Patrick

---

哦，该死。本来希望它更自动化一些。谢谢。 - Jonathan Ong

看起来对我来说非常通用。只需将标题行复制到COPY命令中即可。在任何体面的语言中或甚至手动操作都很容易。 - Patrick

哦，聪明！我怎么在Bash中实现呢？ - Jonathan Ong

嗯，我不是Bash方面的专家，但是可以读取CSV文件直到\n以获取标题行，然后将该值粘贴到COPY命令中。例如使用 head -n 1 _filename_. - Patrick

COPY 命令还有很大的改进空间，自动识别表头会更好。据我所知，目前没有人在开发它。需要进行更高级操作的大多数人使用 ETL 工具。 - Craig Ringer

Answer 2

以下是使用 CSV 文件标题行导入用户的单行示例：

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

或者使用gzip：

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

- Chris Hobbs

---

这个答案正是所需之物。 - étale-cohomology

Answer 3

回答Jonathan在被接受的答案下的评论 - 如果您想要从CSV中“尊重”列顺序加载数据（我有一些具有不同模式迁移历史或缺少列的转储，我想导入）。

如果您想要使用CSV标题将其导入Bash： （我的表名为 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

- Tomasz Gandor