使用ActiveRecord的PostgreSQL大容量插入

Question

我有很多来自MySQL的记录。我对数据进行了修改，以便使用ActiveRecord将其成功插入到PostgreSQL中。我可以很容易地做到这一点，以行为基础的插入，即一次一行。这非常慢，我想进行大容量插入，但如果任何行包含无效数据，则此操作将失败。有没有什么办法可以实现大容量插入，并且只有无效的行会失败，而不是整个大容量？

Answer 1

复制

当使用SQL进行大容量插入(或客户端中的等效\copy )时，失败不是一个选项。COPY不能跳过非法行。您必须将输入格式与导入到的表相匹配。

如果数据本身(而不是装饰器)违反了您的表定义，那么有一些方法可以使其更具容忍性。例如:创建一个包含所有类型为text的列的临时临时表。复制到其中，然后在转换为实际的数据类型并插入到实际的目标表之前，使用SQL命令修复有问题的行。

请考虑以下相关答案：

How to bulk insert only new rows in PostreSQL

或者这个更高级的例子：

"ERROR: extra data after last expected column" when using PostgreSQL COPY

如果NULL NULL值有问题，请暂时从目标表中删除NOT NULL约束。修复复制后的行，然后恢复约束。或者，如果你暂时负担不起软化你的规则，那就走临时表的路线。

示例代码：

ALTER TABLE tbl ALTER COLUMN col DROP NOT NULL;
COPY ...
-- repair, like ..
-- UPDATE tbl SET col = 0 WHERE col IS NULL;
ALTER TABLE tbl ALTER COLUMN col SET NOT NULL;

或者您只需修复源表。COPY告诉你有问题的行号。使用您喜欢的编辑器并修复它，然后重试。我喜欢使用vim来解决这个问题。

INSERT

对于INSERT (如注释)，对NULL值的检查很简单：

要跳过具有NULL值的行：

INSERT INTO (col1, ...
SELECT col1, ...
WHERE col1 IS NOT NULL

插入某物else而不是NULL值(在我的示例中为空字符串)：

INSERT INTO (col1, ...
SELECT COALESCE(col1, ''),  ...

Answer 2

一种常见的解决方法是将数据导入到没有约束的TEMPORARY或UNLOGGED表中，并且在输入中的数据完全是假的情况下，导入text类型的列。

然后，您可以对数据执行INSERT INTO ... SELECT查询，以使用在导入过程中清理数据的大型查询来填充真实的表。为此，您可以使用许多CASE语句。这个想法是在一次传递中转换数据。

在读取数据时，您可以在Ruby语言中完成许多修复，然后使用COPY ... FROM STDIN将数据推送到PostgreSQL。这在Ruby的Pg gem中是可能的，参见例如https://bitbucket.org/ged/ruby-pg/src/tip/sample/copyfrom.rb。

对于更复杂的情况，请查看Pentaho Kettle或Talend Studio ETL工具。