Invalid null byte - field longer than 1 byte

Question

I am copying data from a Redshift Manifest File stored in S3. My copy command looks like

COPY <table name> FROM 's3://...' CREDENTIALS '<credentials>' FORMAT AS JSON 'auto' GZIP TRUNCATECOLUMNS ACCEPTINVCHARS EMPTYASNULL TIMEFORMAT AS 'auto' REGION '<region>' manifest;

The column in the table where I am facing this issue is of type varchar(255). Value of this column in the s3 file looks like

"<column>":"\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000..."

Error: Invalid null byte - field longer than 1 byte

I have tried using NULL AS '\0' as well. That didn't work. The error this gives is Invalid operation: NULL argument is not supported for JSON based COPY

Answer 1

It is not clear why you would want to store a bunch of ascii zero characters in a string so more information on what this is for will get a more useful workaround. The basic answer is 'don't do this'.

Ascii zero is defined as the null terminator character (aka NUL but this is not the same things a NULL) and this character has special meaning in data streams. It's a control character and as such has no business being in your strings.

If you are trying to represent binary data in a string you should base64 encode the data first.

If you are trying to represent NULL this is done with null in the json - "column":null

More information on what you are doing will be helpful in proposing a solution.