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

问题描述

我将 psql 与 PostgreSQL 数据库和以下 copy 命令一起使用:

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

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

我明白了:

ERROR:  extra data after last expected column

如何跳过有错误的行?

推荐答案

你不能跳过错误而不跳过整个命令直到包括 Postgres 14.目前没有更复杂的错误处理.

You cannot skip the errors without skipping the whole command up to and including Postgres 14. 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.

粗体强调我的.并且:

COPY FROM 如果输入文件的任何行包含列比预期多或少.

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

COPY 是一种极其快速的数据导入/导出方式.复杂的检查和错误处理会减慢速度.

COPY is an extremely fast way to import / export data. Sophisticated checks and error handling would slow it down.

尝试在 Postgres 9.0 中向 COPY 添加错误日志 但它从未提交过.

There was an attempt to add error logging to COPY in Postgres 9.0 but it was never committed.

改为修复您的输入文件.

Fix your input file instead.

如果您的输入文件中有一个或多个附加列，并且该文件其他方面是一致的，您可以向表 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 中的特定列

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