
Example:
dbadmin=> CREATE TABLE test (c INT);
CREATE TABLE
dbadmin=> \d test
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
-------+-------+--------+------+------+---------+----------+-------------+------
public | test | c | int | 8 | | f | f |
(1 row)
dbadmin=> \! cat /home/dbadmin/test.txt
""
"1"
dbadmin=> COPY test
dbadmin-> FROM '/home/dbadmin/test.txt'
dbadmin-> ENCLOSED BY '"'
dbadmin-> REJECTED DATA TABLE test_bad;
Rows Loaded
-------------
1
(1 row)
dbadmin=> SELECT c
dbadmin-> FROM test;
c
---
1
(1 row)
dbadmin=> SELECT rejected_data, rejected_reason
dbadmin-> FROM test_bad;
rejected_data | rejected_reason
---------------+--------------------------------------------
"" | Invalid integer format '' for column 1 (c)
(1 row)
Vertica cannot implicitly cast the NULL value to an integer . But we can explicitly using the cast ::!.
dbadmin=> DROP TABLE test_bad;
DROP TABLE
dbadmin=> TRUNCATE TABLE test;
TRUNCATE TABLE
dbadmin=> COPY test (c_filler FILLER VARCHAR(1), c AS c_filler::!INT)
dbadmin-> FROM '/home/dbadmin/test.txt'
dbadmin-> ENCLOSED BY '"'
dbadmin-> REJECTED DATA TABLE test_bad;
Rows Loaded
-------------
2
(1 row)
dbadmin=> SELECT c
dbadmin-> FROM test;
c
---
1
(2 rows)
dbadmin=> SELECT rejected_data, rejected_reason
dbadmin-> FROM test_bad;
rejected_data | rejected_reason
---------------+--------------------------------------------
(0 rows)
Helpful Links:https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/CastFails.htm
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/IgnoringColumnsAndFieldsInTheLoadFile.htm
Have fun!