PostgreSQL: Text to JSONB - total size exceeds the maximum

Question

I've imported a JSON array in a text field and want to convert this to JSONB.

I get the following error

SQL Error [54000]: ERROR: total size of jsonb array elements exceeds the maximum of 268435455 bytes ERROR: total size of jsonb array elements exceeds the maximum of 268435455 bytes ERROR: total size of jsonb array elements exceeds the maximum of 268435455 bytes

The pg_column_size() function returns a size of 59,596,497.

The size of the JSON data was 200 MB on disk before I used the COPY command to import the data into the Postgres database.

How can I get around this? E.g. by calculating the resulting size from my JSON file on disk before importing?

Update: I found the source code for the class that is throwing this exception. Maybe someone with expertise in C can have a look at that.

• GitHub - jsonb_util.c - Line 1569
• GitHub - jsonb_util.c - Line 1589

Answer 1

Text data will be larger when being saved in postgres tables. You can get an approximate value: DATA_SIZE*1,28 2nd Quadrant.

Also, before loading your data select pg_column_size(to_jsonb('[DATA]'::text)) will give you a hint of your expected size.