Rejected Data Table Row Number: Quick Tip

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file.

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!