BUG #14048: copy issues with jsonb
От | rotten@windfish.net |
---|---|
Тема | BUG #14048: copy issues with jsonb |
Дата | |
Msg-id | 20160327150442.2901.36148@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #14048: copy issues with jsonb
|
Список | pgsql-bugs |
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...
В списке pgsql-bugs по дате отправления: