The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Row rejected due to materialized type error on column

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

  • chaimachaima Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.