NULL AS 'string' option of the copy command does not work with fcsvparser()
Posted: Thu Jan 19, 2017 2:43 pm
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?
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?