来自/导入的Postgres CSV COPY不遵守CSV标头 [英] Postgres CSV COPY from/import is not respecting CSV headers

问题描述

我正在尝试将数据从CSV导入表中。问题是，即使使用 CSV HEADER ，也会根据列索引而不是基于该列的标题导入CSV。

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

我的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,

我的COPY命令在bash中如下所示：

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

我得到的错误是：

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

现在，我将重新排列CSV中的列，但这不行吗？

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

推荐答案

默认情况下， COPY 命令按照表中列的默认顺序从CSV文件复制列。 HEADER 选项在输入被忽略，它基本上仅通知后端忽略输入的第一行。如果CSV中的列顺序与表中的列顺序不匹配，则可以显式指定列顺序以匹配CSV文件的布局：

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;

这篇关于来自/导入的Postgres CSV COPY不遵守CSV标头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！