invalid input syntax for integer: "\N" when gpload a text file

Question

I found one strange problem. With greenplum gpload tool, I tried to import data from a single text file to greenplum db. The content of file t1.out is:

\N|24234243
\N|\N
12342|\N

and gpload version is:

gpload version 5.3.0 build commit:2155c5a8cf8bb7f13f49c6e248fd967a74fed591

and the table t1 is as follow:

test=# \d t1;
  Table "public.t1"
  Column |  Type  | Modifiers 
  --------+--------+-----------
   id1    | bigint | 
   id2    | bigint |

when I use gpload with t1.yaml, I got following error:

2019-04-09 20:12:18|WARN|Please use following query to access the detailed error
2019-04-09 20:12:18|WARN|select * from   p_read_error_log('ext_gpload_reusable_b7ef1344_5ac0_11e9_b6fc_fa163e2d09a1') where cmdtime > to_timestamp('1554811937.76')

and when I ran this sql(made small change only selecting two key fields) in postgresql, I got following errors:

    errmsg                       |   rawdata 

invalid input syntax for integer: "\N", column id1 | \N|24234243
invalid input syntax for integer: "\N", column id1 | \N|\N
invalid input syntax for integer: "\N", column id2 | 12342|\N

It showed all the 3 lines were not imported with 'invalid input syntax for integer: "\N"'.

but I can use COPY command to import above 3 items into t1 successfully.

I tried several ways to find why, but failed. Part of my t1.yaml as follows:

    - FORMAT: text 
    - DELIMITER: '|' 
    - ESCAPE: 'OFF' 
    - NULL_AS: '\N' 
    - ERROR_LIMIT: 100

BTW:https://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gpload.html#topic1__cfnullas has shown that the default NULL_AS is \N, and gpload has found \N, and why it failed to mark the two field as NULL? Any help is appreciated!

Brendan Stephens Brendan Stephens · Accepted Answer · 2019-04-09T13:05:59

What version of GPDB are you using? There was a known issue in v5 that was fixed in 5.6+

https://gpdb.docs.pivotal.io/560/relnotes/GPDB_561_README.html

29197 - gpload/ gpfdist

When running a gpload operation, the gpfdist utility did not recognize a \N as the NULL character when the gpload configuration file specified a null character null_as: '\N'. When processing the configuration file, the gpload utility incorrectly escaped the backslash () with another backslash. This issue has been resolved. The gpload utility has been improved to properly handle a backslash when processing the null_as property.

invalid input syntax for integer: "\N" when gpload a text file

1 Answers