问题:从 PostgreSQL 中的制表符分隔文本文件导入时如何忽略某些行?

我有一个 30 GB 的制表符分隔文本文件,其中包含超过 1 亿行,当我想使用 \copy 命令将此文本文件导入 PostgreSQL 表时,某些行会导致错误。如何在导入到 postgresql 时忽略这些行并记录被忽略的行?

我通过 SSH 连接到我的机器,所以我不能使用 pgadmin!

在导入之前很难编辑文本文件,因为很多不同的行都有不同的问题。如果有一种方法可以在导入之前逐一检查行,然后对各个行运行 \copy 命令,那将很有帮助。

下面是生成表格的代码:

CREATE TABLE Papers(
    Paper_ID CHARACTER(8) PRIMARY KEY,
    Original_paper_title TEXT,
    Normalized_paper_title TEXT,
    Paper_publish_year INTEGER, 
    Paper_publish_date DATE,
    Paper_Document_Object_Identifier TEXT,
    Original_venue_name TEXT,
    Normalized_venue_name TEXT,
    Journal_ID_mapped_to_venue_name CHARACTER(8),
    Conference_ID_mapped_to_venue_name CHARACTER(8),
    Paper_rank BIGINT,
    FOREIGN KEY(Journal_ID_mapped_to_venue_name) REFERENCES Journals(Journal_ID),
    FOREIGN KEY(Conference_ID_mapped_to_venue_name) REFERENCES Conferences(Conference_ID));

解答

不要直接加载到目标表,而是加载到单列临时表。

create table Papers_stg (rec text);

加载完所有数据后,您可以使用 SQL 对数据进行验证。

查找字段数错误的记录:

select  rec
from    Papers_stg
where   cardinality(string_to_array(rec,'       ')) <> 11

创建一个包含所有文本字段的表格

create table Papers_fields_text
as
select  fields[1]  as Paper_ID                          
       ,fields[2]  as Original_paper_title              
       ,fields[3]  as Normalized_paper_title            
       ,fields[4]  as Paper_publish_year                
       ,fields[5]  as Paper_publish_date                
       ,fields[6]  as Paper_Document_Object_Identifier  
       ,fields[7]  as Original_venue_name               
       ,fields[8]  as Normalized_venue_name             
       ,fields[9]  as Journal_ID_mapped_to_venue_name   
       ,fields[10] as Conference_ID_mapped_to_venue_name
       ,fields[11] as Paper_rank                        

from   (select  string_to_array(rec,'       ')  as fields
        from    Papers_stg
        ) t
where   cardinality(fields) = 11

对于字段转换检查,您可能希望使用此处描述的概念