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

Liam_Clarke-Hut

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

Sajan_Sangraula

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

Liam_Clarke-Hut

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