COPY INTO is failing due to a double quote being found within the data value.
The two double quotes around C causes the failure -> NE 845 "C" Street.
The exact error message is:
Found character 'H' instead of field delimiter '|~' File '@~/FolderX/datafile.dat.gz', line 1, character 107 Row 274598, column "MY_TABLE"["BADCOLUMN":20] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
The Format File is defined as:
CREATE FILE FORMAT "DW"."STG".FMT_FILE
COMPRESSION = 'GZIP'
FIELD_DELIMITER = '|~'
RECORD_DELIMITER = '\n'
SKIP_HEADER = 0
FIELD_OPTIONALLY_ENCLOSED_BY = '\042'
TRIM_SPACE = TRUE
ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
ESCAPE = '#'
ESCAPE_UNENCLOSED_FIELD = 'NONE'
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
ENCODING = 'WINDOWS1253'
NULL_IF = ('\\N');
Why does it care if double quote exists within any value when the data is:
123|~NE 845 "C" Street|~PULLMAN
There should be no need, given the definition of the format file, add an escape within any string. This failure seems like a bug.
