Re: BUG #14048: copy issues with jsonb

On Sun, Mar 27, 2016 at 7:25 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Sun, Mar 27, 2016 at 8:04 AM, <rotten@windfish.net> wrote:
>
>> 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.
>
> =E2=80=8B[...]
>>
>
>
>> 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...
>>
>
> =E2=80=8BI'm reasonably certain pg_dump/pg_restore makes use of "COPY" to=
 perform
> its work - at least for custom format and plain non-inserts mode=E2=80=8B=
 - so if
> that works but whatever you are doing manually does not there is some
> degree of suspicion that what you are doing may be incorrect.
>
> We really need a sample record and a self-contained script that you can
> make reproduce this behavior.
>
>
=E2=80=8BRick provided a file offline which I peeked at.  Responding to him
privately but lacking further public comments this report can be considered
"Not a bug".

David J.