Re: [GENERAL] Removing null bytes from a json column - Mailing list pgsql-general

From Tom Lane
Subject Re: [GENERAL] Removing null bytes from a json column
Date
Msg-id 28018.1497193323@sss.pgh.pa.us
Whole thread Raw
In response to [GENERAL] Removing null bytes from a json column  (Timothy Garnett <tgarnett@panjiva.com>)
List pgsql-general
Timothy Garnett <tgarnett@panjiva.com> writes:
> Does anyone have some tips on how to deal with an existing json type column
> that has some null bytes ( \u0000) in it? It seems like anything I do that
> touches any row with a null byte just errors.  I'd love to just remove them
> if I could find some way to find them, but I'm having trouble even figuring
> out how to do that.

Doesn't it work to cast to text and do a LIKE or regex search?

regression=# select '{"z":"\u0000"}'::json::text ~ '\\u0000';
 ?column?
----------
 t
(1 row)

regression=# select '{"z":"\u0001"}'::json::text ~ '\\u0000';
 ?column?
----------
 f
(1 row)

It's true that we won't let you cast such a value to JSONB or do any
exciting JSON-ish manipulations on it, but I'm not seeing an error
in cast-to-text.

            regards, tom lane


pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) fortoast value 76753264 in pg_toast_10920100
Next
From: pinker
Date:
Subject: Re: [GENERAL] Huge Pages - setting the right value