The following bug has been logged on the website:
Bug reference: 14048
Logged by: Rick Otten
Email address: rotten@windfish.net
PostgreSQL version: 9.5.1
Operating system: Ubuntu 14.04
Description:
I have a few jsonb columns in a table and I've run into several issues with
COPY with that column type. I didn't see anything specific about it in the
to-do list, and Google didn't turn up anyone else specifically complaining,
so I'm filing it here. I apologize if this is a known issue.
The table is the backend for a web pixel (tracking) service. It has 4 jsonb
columns which contain variable data about the browser and session. (parsed
user agent, list of cookies, query parameters, etc...)
I wanted to copy a section of the table from one 9.5.1 database to another.
For some reason the CSV and TEXT format exports do not quote/escape the
nested JSON very well - after several tries, I gave up on that approach.
The "copy to" output appears to be too mangled. It was not obvious, at
first glance, where it is getting confused.
Next I tried BINARY mode. That worked ok for the rows I was moving last
week, but this week (for a different piece of the table) I'm getting the
following when I try to read it back in:
# copy (select * from pixels.pixel_requests where server_timestamp >
'2016-03-26 00:00') to '/home/rotten/pixels27Mar2016.bin' binary;
# copy pixels.pixel_requests_tmp_2 from '/home/rotten/pixels27Mar2016.bin'
binary;
ERROR: unsupported jsonb version number 63
CONTEXT: COPY pixel_requests_tmp_2, line 1, column parameters
The new tmp table has the same ddl as the original table. Both tables are
UTF-8 enabled. Both are the same version of PostgreSQL running on the same
version of Ubuntu.
I'm willing to share the copy output (313M - 288,000 rows) with someone who
is able to help troubleshoot the issue, but I don't want to post the data in
a public forum.
I'm not sure which row and column is causing the copy command to fail.
(There is already a to-do that may help with that.)
Meanwhile, I'll use pg_dump to get the rows I need. Hopefully that will be
able to extract and reload the jsonb data ok. pg_dump hasn't been a problem
in the past anyway...