在使用带有netezza的nzload时，我们如何定义多个空值？

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.

我正在尝试将数据插入到netezza框中。我有一个管道分隔文件,其中我想要'none'的多种变体(即'None','NONE','none')被视为NULL。虽然'-nullvalue'选项一次适用于一个变体,但它不允许我将多个变体设置为NULL。

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

此外,cf中的nullvalue选项的多个定义也无济于事

1 个解决方案

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

从版本7.2开始,nzload不允许-nullvalue选项使用多个值。但是,您可以指定的4个字符值不区分大小写,这允许您的特定样本情况-nullvalue为'None'以匹配'NONE','none','NoNe'等。

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)