COPY - reject a row when a value is NULL instead of rolling back?

Hi,

We bulk load data into a table with a not-null constraint on an integer column that stores IPs that have been converted with INET_ATON().

For example:
 COPY counters (        
        slot_id,
        ad_id,
        date FORMAT 'YYYY.MM.DD HH:MI:SS.MS',
        source_ip_name FILLER VARCHAR(15),
        source_ip AS INET_ATON(source_ip_name)
) ...
However, if an invalid IP is reached, which is an occasional issue, INET_ATON() returns NULL and the not-null constraint is breached, and the entire COPY is then rolled back.

I would like to have these rows rejected as if the data underlying source_ip was the wrong data-type - that way, Vertica rejects the invalid row, but imports all the other valid rows.

Currently we have to pre-validate the records. Are there any directives that can change this behaviour?

Alternatively, if anyone can think of a way to somehow pass a wrong data type to source_ip. e.g., a char instead of an integer, this would then trigger the desired row rejection behaviour. I've given it my best shot, but no joy.

Cheers, 

Liam Clarke

Comments

  • Hi Liam,
    You should be able to define a filler column in your copy statement and cast the null value to a wrong data type:

    COPY counters (       
            slot_id,
            ad_id,
            date FORMAT 'YYYY.MM.DD HH:MI:SS.MS',
            source_ip_name FILLER VARCHAR(15),
            source_ip_temp filler varchar(64),
            source_ip AS isnull(INET_ATON(source_ip_name),'##')
    ) ...


    /Sajan
  • Hi Sajan, 

    Unfortunately, that approach does not work as isnull() seeks to unify the types of the two expressions. E.g.,
    select isnull(inet_aton('asasa'), 'xx');
    ERROR 3681:  Invalid input syntax for integer: "xx"
    Regards,

    Liam Clarke

Leave a Comment

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