How do we define multiple null values while using nzload with netezza?

Question

I am trying insert data into the netezza box. I have a pipe-delimited file wherein I want multiple variations of 'none'(i.e. 'None', 'NONE','none') be treated as NULL. While '-nullvalue' option does work for one variation at a time it doesn't allow me to set multiple variations to be treated as NULL.

Moreover multiple dfinition of the nullvalue option in the cf too doesn't help

@vkp But I am not filtering it field wise, nzload does it for all the fields at one go. — user2899427
– user2899427, Commented Jul 20, 2015 at 19:41

ScottMcG · Accepted Answer · 2015-07-21 02:31:50Z

1

As of version 7.2, nzload does not allow multiple values for the -nullvalue option. However, the 4 character value that you can specify is case insensitive, which allows for your particular sample case of -nullvalue of 'None' to match with 'NONE', 'none', 'NoNe', etc.

TESTDB.ADMIN(ADMIN)=> create table null_test (col1 int, col2 int, col3 int);
CREATE TABLE

$ cat test.txt
1|NONE|1
2|None|2
3|NoNe|3


$ nzload -db testdb -df test.txt -t null_test -delim \| -nullvalue 'None'
Load session of table 'NULL_TEST' completed successfully

TESTDB.ADMIN(ADMIN)=> select * from null_test where col2 is null;
 COL1 | COL2 | COL3
------+------+------
    1 |      |    1
    2 |      |    2
    3 |      |    3
(3 rows)

answered Jul 21, 2015 at 2:31

ScottMcG

3,8972 gold badges14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do we define multiple null values while using nzload with netezza?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related