从CSV执行COPY时自动生成ID列 [英] Autogenerating ID column when doing COPY from CSV

问题描述

我有一个简单的表（4个文本列和一个ID列）。我正在尝试导入没有ID列的CSV文件。

I have a simple table (4 text columns, and an ID column). I am trying to import my CSV file which has no ID column.

在Postico中，我的架构设置如下：

In Postico I have the schema setup as such:

DROP TABLE changes;
CREATE TABLE changes(
id    SERIAL PRIMARY KEY,
commit_id TEXT,
additions INTEGER,
deletions INTEGER,
file_id TEXT
);
CREATE TEMP TABLE tmp_x AS SELECT * FROM changes LIMIT 0;
COPY tmp_x(commit_id,additions,deletions,file_id) FROM '/Users/George/git-parser/change_file' (format csv, delimiter E'\t');
INSERT INTO changes SELECT * FROM tmp_x
ON CONFLICT DO NOTHING;
DROP TABLE tmp_x;

但是我收到错误 ERROR： id列中为空值违反非空约束

推荐答案

您需要指定列：

COPY tmp_x (commit_id, additions, deletions, file_id) 
FROM '/Users/George/git-parser/change_file' (format csv, delimiter E'\t');

副本中指定的列顺序语句必须与输入文件中的列顺序明显匹配。

The order of columns specified in the copy statement must obviously match the order of the columns in the input file.

您需要更改插入语句。

INSERT INTO changes SELECT * FROM tmp_x

将从 tmp_x 中插入 all 列到目标表中，但是由于您没有定义 id 列为 tmp_x 表中的 serial 列，未生成任何内容，并且插入了 null 值。而您的 insert 语句仅复制这些空值。

will insert all columns from tmp_x into the target table, but as you did not define the id column as serial in the tmp_x table, nothing got generated and null values were inserted. And your insert statement just copies those null values.

您需要跳过插入 id 列>语句：

You need to skip the id column in the insert statement:

INSERT INTO changes (commit_id,additions,deletions,file_id) 
SELECT commit_id,additions,deletions,file_id
FROM tmp_x
ON CONFLICT DO NOTHING;

您实际上可以从中删除 id 列 tmp_x

You can actually remove the id column from tmp_x

这篇关于从CSV执行COPY时自动生成ID列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！