Row rejected due to materialized type error on column
phil2
✭
Hello.
I'm trying to load data from rfc4180 csv file using COPY like this:
copy public.tmp_items_search from stdin PARSER FCSVParser(type='rfc4180') REJECTED DATA AS TABLE "rej_items_search_test" DIRECT COMMIT
And everything works fine untill there is a string with a length bigger than a size specified in column definition.
Im getting weird error
Row [3] rejected due to materialized type error on column: [ref] with value: [https://....].
So I try reject_on_materialized_type_error=FALSE like this
copy public.tmp_items_search from stdin PARSER FCSVParser(type='rfc4180',reject_on_materialized_type_error=FALSE,header=TRUE) REJECTED DATA AS TABLE "rej_items_search_test" DIRECT COMMIT
And it does not work too. Looks like reject_on_materialized_type_error takes no effect.
Does anybody know how to make vertica to ignore just a single value instead of a whole row.
Or maybe there is an option to cut value length for a column during copy.
Comments
Hi,
Are you loading into a columnar table or flex table?
Materialization errors happen when the data value cannot be coerced to the target column data type, such as converting an arbitrary string to a DATE value. In your case, it is due to column length (value doesn't fit into your ref column) as you pointed out. The parameter reject_on_materialized_type_error (set to FALSE by default) allows you to load data regardless of materialization errors. When they happen, the target column gets a NULL value and load keeps going.
Ariel
Hello,
I'm loading data into columnar table.
And it looks like reject_on_materialized_type_error does not take any effect when type error happens on columnar table, a row get rejected anyway.
Is it a bug? Or maybe you can point me out where I'm doing something wrong.
You're right. reject_on_materialized_type_error does not effect columnar tables. That looks like an omission. I will file an internal ticket to investigate that.
Could you tell us what version you're running so we can accurately target a fix if applicable?
For now, your options are: 1) Enlarge the conflicting column so data will fit in, 2) Load data into a flex table (with the same real columns of public.tmp_items_search).
Thanks for your advice. Flex table with real columns seems to solve issue for me. I'm running Vertica Analytic Database v8.1.1-4
If anyone comes across the same issue, this was fixed in 9.1SP1