Unicode error with \u0000 on COPY of large JSON file into Postgres

Question

I'm using Postgres 9.4 on Ubuntu 14.04.

I have a 30GB JSON file that I'm trying to COPY into Postgres.

But I keep getting the following error:

COPY <table>(comment_jsonb) FROM '<json file>' WITH (format csv, quote e'\x01', delimiter e'\x02', escape e'\x01');
ERROR: unsupported Unicode escape sequence
DETAIL: \u0000 cannot be converted to text.

I tried searching for the offending Unicode string to remove it, but because it's \u0000, which is NULL, I can't seem to get a regex to work. And when I print it into console, I have no idea where the NULL is, because (at least my guess is) it seems to print as nothing (though I have no idea what it is actually doing).

Is there a way to skip these errors?

Alternatively, how could I replace any instances of that Unicode in my JSON file?

• chirlu: The NUL character can only be present in a JSON string as the escape sequence \u0000. You should have no trouble searching for that string; you may only need to escape the backslash, often by doubling it. Alternatively (and because this is a database site), you could use the json column type. Its performance is less good than jsonb, but it is fine with NUL.

Answer 1

In a (perhaps) similar case I had been confronted with \u0000 in a string I needed to process as JSON. The follow replace worked for me:

regexp_replace(stringWithNull, '\\u0000', '', 'g')

Hope this helps.