Page 1 of 1

NULL AS 'string' option of the copy command does not work with fcsvparser()

Posted: Thu Jan 19, 2017 2:43 pm
by JimKnicely
Howdy,

I am trying to use the NULL AS 'string' option of the copy command but am running into an issue...

dbadmin=> \! cat /home/dbadmin/test.txt
1
2
NA
3
4
NA

dbadmin=> \d abc.test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------+------+---------+----------+-------------+-------------
abc | test | a | float | 8 | | f | f |
(1 row)


So the NULL AS 'NA' works okay if I use the default parser:

dbadmin=> copy abc.test from '/home/dbadmin/test.txt' null 'NA' direct;
Rows Loaded
-------------
6
(1 row)

dbadmin=> select * from abc.test;
a
---
1
2
3
4


(6 rows)


But if I use the fcsvparser, it does not:

dbadmin=> truncate table abc.test;
TRUNCATE TABLE

dbadmin=> copy abc.test from '/home/dbadmin/test.txt' parser fcsvparser() null 'NA' rejected data table abc.bad_data;
Rows Loaded
-------------
3
(1 row)

dbadmin=> select * from abc.test;
a
---
2
3
4
(3 rows)

dbadmin=> select * from abc.bad_data;
node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason
----------------+------------------------+-------------------------------+-------------------+--------------+--------------+------------+---------------+---------------------------+-------------------------
v_p11_node0001 | /home/dbadmin/test.txt | v_p11_node0001-132659:0x8b4a4 | 45035996273869442 | 1 | 0 | 2 | NA | 2 | Parse error in column 1
v_p11_node0001 | /home/dbadmin/test.txt | v_p11_node0001-132659:0x8b4a4 | 45035996273869442 | 1 | 0 | 5 | NA | 2 | Parse error in column 1
(2 rows)



How can I fix this?

Re: NULL AS 'string' option of the copy command does not work with fcsvparser()

Posted: Sun Jun 04, 2017 3:48 pm
by JimKnicely
Follow up:

If you click on the link below, you will find an example of “Using the NULL Parameter” with the FCSVPARSER…

https://my.vertica.com/docs/8.1.x/HTML/ ... SVData.htm

Unfortunately, it only appears to work with FLEX tables ...

dbadmin=> create flex table tt();
CREATE TABLE

dbadmin=> COPY tt FROM '/home/dbadmin/tt.txt' PARSER fcsvparser() delimiter '|' NULL 'NULL' REJECTED DATA TABLE tt_rejected;
Rows Loaded
-------------
3
(1 row)

dbadmin=> SELECT compute_flextable_keys_and_build_view('tt');
compute_flextable_keys_and_build_view
------------------------------------------------------------------------------------------
Please see public.tt_keys for updated keys
The view public.tt_view is ready for querying
(1 row)

dbadmin=> select * from public.tt_view;
a | b | c1
----+----+----
10 | 20 |
20 | 30 | 50
20 | 30 | 40
(3 rows)