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.

Ariel_Cary

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

phil2

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.

Ariel_Cary

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).

phil2

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

chaima

If anyone comes across the same issue, this was fixed in 9.1SP1