Row rejected due to materialized type error on column

Forum|Forum|8 years ago
April 24, 2018
5 replies
8 views

+2

phil2
Participating Frequently

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
Participating Frequently
Forum|Forum|8 years ago
April 24, 2018

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

Like

+2

phil2
Author
Participating Frequently
Forum|Forum|8 years ago
April 25, 2018

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.

Like

Ariel_Cary
Participating Frequently
Forum|Forum|8 years ago
April 25, 2018

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