postgresql 如何使用 psql \copy 元命令忽略错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36634360/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to ignore errors with psql \copy meta-command
提问by Superdooperhero
I am using psqlwith a PostgreSQL database and the following copycommand:
我正在使用psqlPostgreSQL 数据库和以下copy命令：
\COPY isa (np1, np2, sentence) FROM 'c:\Downloads\isa.txt' WITH DELIMITER '|'
I get:
我得到：
ERROR: extra data after last expected column
How can I skip the lines with errors?
如何跳过有错误的行？
回答by Erwin Brandstetter
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.
如果不跳过包括 Postgres 9.5 在内的整个命令，就无法跳过错误。目前没有更复杂的错误处理。
\copyis just a wrapper around SQL COPYthat channels results through psql. The manual for COPY:
\copy只是 SQL 的一个包装器，COPY它通过 psql 引导结果。手册COPY：
COPYstops operation at the first error.This should not lead to problems in the event of aCOPY TO, but the target table will already have received earlier rows in aCOPY 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 invokeVACUUMto recover the wasted space.
COPY在第一个错误处停止操作。这应该不会在 a 的情况下导致问题COPY TO，但目标表将已经收到 a 中较早的行COPY FROM。这些行将不可见或不可访问，但它们仍会占用磁盘空间。如果故障发生在大型复制操作中，这可能会浪费大量磁盘空间。您可能希望调用VACUUM以恢复浪费的空间。
Bold emphasis mine. And:
大胆强调我的。和：
COPY FROMwill raise an error if any line of the input file contains more or fewer columns than are expected.
COPY FROM如果输入文件的任何行包含的列比预期的多或少，则会引发错误。
There was an attempt to add error logging to COPYin 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.
曾尝试COPY在 Postgres 9.0 中添加错误日志记录，由 Aster Data 带头，但从未提交。该公司后来被 Teradata 收购，所以我怀疑他们是否仍在追求该项目。
Solution
解决方案
Fix your input file instead.
改为修复您的输入文件。
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 isaand drop those afterwards. Or (cleaner with production tables) import to a temporary staging table and INSERTselected columns (or expressions) to your target table isafrom there.
如果您的输入文件中有一个或多个附加列，并且该文件在其他方面是一致的，您可以向表中添加虚拟列，isa然后再删除这些列。或者（使用生产表进行清理）从那里导入到临时登台表和INSERT选定的列（或表达式）到您的目标表isa。
Related answers with detailed instructions:
带有详细说明的相关答案：
