如何使用psql的\copy元命令忽略错误

Question

我正在使用 PostgreSQL 数据库，并使用 psql 命令，使用以下的 copy 命令：

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

我理解为：

ERROR:  extra data after last expected column

我该如何跳过出错的行？

- Superdooperhero

Answer 1

如果您跳过错误，则需要跳过整个命令，包括Postgres 14。目前没有更复杂的错误处理。

\copy只是围绕SQL COPY的包装器，通过psql通道传输结果。 COPY的手册：

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

我加粗了部分内容。并且：

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

COPY是一种非常快速的导入/导出数据的方法。复杂的检查和错误处理会减慢它的速度。

Postgres 9.0中曾经有一个尝试为COPY添加错误日志记录，但它从未被提交。

解决方案

修复您的输入文件。

如果您的输入文件中有一个或多个附加列，并且该文件其余部分保持一致，则可以向您的表isa添加虚拟列，然后再删除这些列。(对于生产表来说，更干净的方法是)导入到临时暂存表中，然后从那里INSERT所选列(或表达式)到您的目标表isa。

相关答案详细说明:

• 如何在Postgres中使用CSV文件更新选定行的值？
• COPY命令：仅从CSV复制特定列

- Erwin Brandstetter

---

如果我添加了额外的表列，我是否可以使用类似于我的原始命令（带有额外的列），还是需要为可选列添加其他选项？ - Superdooperhero

我收到了一个错误：缺少“dummy1”列的数据。 - Superdooperhero

@Superdooperhero：就COPY而言，它们将像其他列一样成为列。您的“缺失数据”错误表明您的输入文件不一致 - 或者您没有使用正确的分隔符或转义字符。无论哪种方式，我更愿意选择我提到的第二个选项：临时暂存表。但是，您也需要一个一致的文件。 - Erwin Brandstetter

对于那些在未来数年内看到这篇答案的人：如果你的输入数据只有几行格式不正确，你可以运行\copy命令，PostgreSQL将报告错误发生的行号。然后，你可以使用sed -i '5d' input.tsv（其中5是行号）删除此行，并尝试再次运行\copy。 - Botond Balázs

对于阅读Balazs解决方案的任何人，它都不起作用。错误报告中的哪一行格式不正确将无法与输入文件中的同一行协调。您将不得不使用grep搜索来查找该行并使用sed删除它。即，它报告了错误行283678，但是在文件中通过grep搜索后发现其为126092。 - NonReformed Bayesianist

Answer 2

很遗憾，在25年的时间里，Postgres没有为COPY命令提供-ignore-errors标志或选项。在这个大数据时代，你会收到许多不干净的记录，修复每个异常值可能对项目来说非常昂贵。

我不得不采用以下方法来解决问题：

1. 拷贝原始表并将其命名为dummy_original_table
2. 在原始表中创建如下触发器：

    CREATE OR REPLACE FUNCTION on_insert_in_original_table() RETURNS trigger AS  $$  
    DECLARE
        v_rec   RECORD;
    BEGIN
        -- we use the trigger to prevent 'duplicate index' error by returning NULL on duplicates
        SELECT * FROM original_table WHERE primary_key=NEW.primary_key INTO v_rec;
        IF v_rec IS NOT NULL THEN
            RETURN NULL;
        END IF; 
        BEGIN 
            INSERT INTO original_table(datum,primary_key) VALUES(NEW.datum,NEW.primary_key)
                ON CONFLICT DO NOTHING;
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        RETURN NULL;
    END;

3. 将数据复制到虚拟表中。不会在虚拟表中插入记录，但所有记录都将插入原始表中。

psql dbname -c \copy dummy_original_table(datum,primary_key) FROM '/home/user/data.csv' delimiter E'\t'

- Nulik

Answer 3

这里有一个解决方案——逐行导入批处理文件。虽然性能可能会慢得多，但对于您的情况可能已经足够了：

#!/bin/bash

input_file=./my_input.csv
tmp_file=/tmp/one-line.csv
cat $input_file | while read input_line; do
    echo "$input_line" > $tmp_file
    psql my_database \
     -c "\
     COPY my_table \
     FROM `$tmp_file` \
     DELIMITER '|'\
     CSV;\
    "
done

此外，您可以修改脚本以捕获psql的stdout/stderr和退出状态，如果退出状态为非零，则将$input_line和捕获的stdout/stderr回显到stdin和/或追加到文件中。

- Rob Bednark

Answer 4

解决方法：使用sed删除报告的错误行，然后再次运行\copy 后续版本的Postgres（包括Postgres 13）将报告错误的行号。 然后，您可以使用sed删除该行，并再次运行\copy，例如：

#!/bin/bash
bad_line_number=5  # assuming line 5 is the bad line
sed ${bad_line_number}d < input.csv > filtered.csv

[来自@Botond_Balázs的评论]

- Rob Bednark

---

这是行不通的，复制的错误行号与文件中的行号不匹配。当错误报告时需要做一些处理。我已经通过实时数据进行了确认。'错误：第283678行' 实际上是第126092行 '错误：第855531行' 实际上是第642693行。因此这不是一个好的解决方案。 - NonReformed Bayesianist