
Be aware that when a COPY encounters an empty line while loading data, the line is neither inserted nor rejected, but COPY increments the line record number. However, the ROW_NUMBER in the rejected data table doesn’t take into account empty lines. When trying to match the ROW_NUMBER in the rejected data table to the row number in the input file, make sure to discount the blank lines.
Example:
dbadmin=> \! cat -n /home/dbadmin/test.txt
1 1|A|2
2 2|B|4
3 A|D|7
4 A|E|7
5
6
7 6|A|3
8 B|A|3
dbadmin=> \d test
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
-------+-------+--------+------------+------+---------+----------+-------------+
public | test | c1 | int | 8 | | f | f |
public | test | c2 | varchar(1) | 1 | | f | f |
public | test | c3 | int | 8 | | f | f |
(3 rows)
dbadmin=> SELECT row_number,
dbadmin-> rejected_data,
dbadmin-> rejected_reason
dbadmin-> FROM test_bad;
row_number | rejected_data | rejected_reason
------------+---------------+----------------------------------------------
3 | A|D|7 | Invalid integer format 'A' for column 1 (c1)
4 | A|E|7 | Invalid integer format 'A' for column 1 (c1)
6 | B|A|3 | Invalid integer format 'B' for column 1 (c1)
(3 rows)
From the input file we see the third error is on row number 8, but the rejected data table reports the row number as 6.Let’s ignore the blank lines in the input file to match up the row numbers properly!
dbadmin=> \! sed '/^$/d' /home/dbadmin/test.txt | cat -n
1 1|A|2
2 2|B|4
3 A|D|7
4 A|E|7
5 6|A|3
6 B|A|3
Helpful Link:https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/SavingRejectionsTable.htm
Have fun!