COPY issue loading enclosed data with NULLs
I am trying to load file, which columns are enclosed by double quote ("), and delimiter is a pipe (|). Null is empty string Target table TEST.ATTRIB created without NOT NULL constraints, allowing to have NULL in any column. Example of the file I am loading: "27713"|"2"|"KCWAG_PACKSIZE"|"KCWAG_PACKSIZE"|"KCWAG_PACKSIZE"|""|"1" "27814"|"2"|"PROMOTED_GROUPING"|"PromotedGroupin"|"Promoted_Grouping"|"PROMOTED_GROUPING_KEY"|"" The first row has NULL (empty string) in column 6 Second row has NULL value in the last column, column 7 This is a command I used to load the data: copy TEST.ATTRIB from LOCAL '/home/oleg/ATTRIB.txt' enclosed by '"' delimiter '|' NULL '' REJECTED DATA '/tmp/oleg/ATTRIB_rejected.txt' EXCEPTIONS '/tmp/oleg/ATTRIB_exceptions.txt' direct; From above rows, COPY loaded only 1st row. Second failed with error: COPY: Input record 2 has been rejected (Invalid integer format '' for column 7 (DEF_ATTRIB_DATA_TYPE_KEY)). Please see /tmp/oleg/ATTRIB_rejected.txt, record 1 for the rejected record. This record was read from ATTRIB.txt But, COPY loaded 1st row correctly, accepting column 6 as NULL value. And COPY failed to load second row having NULL at the last column. Please do not suggest not to use "enclosed by" as we have many data to load where we have to use enclosure option. Please advise.