How to ignore some rows while importing from a tab separated text file in PostgreSQL?

Question

I have a 30 GB tab separated text file which has more than 100 million rows, when I want to import this text file to a PostgreSQL table using \copy command, some rows cause error. how can I ignore those rows and also take a record of the ignored rows while importing to postgresql?

I connect to my machine by SSH so I can not use pgadmin!

it's very hard to edit the text file before importing because so many different rows have different problems. if there exists a way to check the rows one by one before importing and then run the \copy command for individual rows, it would be helpful.

Below is the code which generates the table:

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));

Answer 1

Don't load directly to your destination table but to a single column staging table.

create table Papers_stg (rec text);

Once you have all the data loaded you can the do verifications on the data using SQL.

Find records with wrong number of fields:

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

Create a table with all text fields

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

For fields conversion checks you might want to use the concept described here

Answer 2

Your only option is to use row-by-row processing. Write shell script (for example) that will loop thru input file and send each row to "copy" then check execution result, then write failed rows to some "err_input.txt".

More complicated logic can increase processing speed. Using "portions" instead of row-by-row and use row-by-row logic on failed segments.

Answer 3

Consider using pgloader

Check BATCHES AND RETRY BEHAVIOUR

Answer 4

You could use an BEFORE INSERT - trigger and check your criteria. If the record fails the check, write a log (or an entry into a separate table) and return null. You could even correct some values, if possible and feasible.

Of course, if checking criteria requires other queries (like finding duplicate keys etc.), you might get a performance issue. But I'm not sure which kind of "different problems in different rows" you mean...

Confer also an answer on StackExchange Database Administrators, and the following example taken from Bartosz Dmytrak at PostgreSQL forum:

CREATE OR REPLACE FUNCTION "myschema"."checkTriggerFunction" ()
RETURNS TRIGGER
AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM "myschema".mytable WHERE "MyKey" = NEW."MyKey")
THEN
 RETURN NULL;
ELSE
 RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql;

and trigger:
CREATE TRIGGER "checkTrigger"
  BEFORE INSERT
  ON "myschema".mytable
  FOR EACH ROW
  EXECUTE PROCEDURE "myschema"."checkTriggerFunction"();