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?
NULL AS 'string' option of the copy command does not work with fcsvparser()
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
NULL AS 'string' option of the copy command does not work with fcsvparser()
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: NULL AS 'string' option of the copy command does not work with fcsvparser()
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: NULL AS 'string' option of the copy command does not work with fcsvparser()
Hi!
No for Vertica 8.0.6.
No for Vertica 8.0.6.