如何使用psql \copy元命令忽略错误 [英] How to ignore errors with psql \copy meta-command

问题描述

我正在使用 psql 与PostgreSQL数据库和以下复制命令：

I am using psql with a PostgreSQL database and the following copy command:

\COPY isa (np1, np2, sentence) FROM 'c:\Downloads\isa.txt' WITH DELIMITER '|'

我得到：

ERROR:  extra data after last expected column

我如何跳过错误的行？ p>

How can I skip the lines with errors?

推荐答案

您不能跳过错误，而不会跳过整个命令，包括Postgres 9.5。目前没有更复杂的错误处理。

You cannot skip the errors without skipping the whole command up to and including Postgres 9.5. There is currently no more sophisticated error handling.

\copy 只是围绕SQL COPY 通过结果PSQL。 COPY 的手册：

COPY 在第一个错误中停止操作。如果 COPY TO ，则不应导致
问题，但是目标表已经
已经在 COPY FROM 中收到较早的行。这些行不会是
可见或可访问，但它们仍然占用磁盘空间。如果
的故障发生在大型复制操作中，这可能会导致相当多的浪费磁盘空间。您可能希望调用
VACUUM 来恢复浪费的空间。

COPY stops operation at the first error. This should not lead to problems in the event of a COPY TO, but the target table will already have received earlier rows in a COPY FROM. These rows will not be visible or accessible, but they still occupy disk space. This might amount to a considerable amount of wasted disk space if the failure happened well into a large copy operation. You might wish to invoke VACUUM to recover the wasted space.

<大胆强调我的。和：

Bold emphasis mine. And:

COPY FROM 将会在输入文件的任何一行引发错误包含
比预期更多或更少的列。

COPY FROM will raise an error if any line of the input file contains more or fewer columns than are expected.

有一个 COPY ，但是它从来没有犯过该公司后来被Teradata收购，所以我怀疑他们还在追求这个项目。

There was an attempt to add error logging to COPY in Postgres 9.0, spearheaded by Aster Data, but it was never committed. The company was later acquired by Teradata, so I doubt they are still pursuing the project.

您的输入文件改为。

如果您的输入文件中有一个或多个附加列，并且文件否则一致，则可以向表中添加虚拟列code> isa ，然后删除它们。或者（使用生产表清理）导入到临时暂存表，并将 INSERT 选定的列（或表达式）导入目标表 isa 从那里。

If you have one or more additional column in your input file and the file is otherwise consistent, you might add dummy columns to your table isa and drop those afterwards. Or (cleaner with production tables) import to a temporary staging table and INSERT selected columns (or expressions) to your target table isa from there.

相关答案与详细说明：

• 如何更新所选具有Postgres中CSV文件值的行


• COPY命令：仅复制csv中的特定列

• How to update selected rows with values from a CSV file in Postgres?
• COPY command: copy only specific columns from csv

这篇关于如何使用psql \copy元命令忽略错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！