Re: BUG #14048: copy issues with jsonb

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: rotten(at)windfish(dot)net
Cc: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #14048: copy issues with jsonb
Date: 2016-03-28 02:25:26
Message-ID: CAKFQuwbrtd5CFwELg=9MaNyvQRUPCyUFL8F_3QW1RsB1TGg8Fg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Sun, Mar 27, 2016 at 8:04 AM, <rotten(at)windfish(dot)net> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14048
> Logged by: Rick Otten
> Email address: rotten(at)windfish(dot)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.

​[...]
>

> 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...
>

​I'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​ - 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.

David J.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Srivathson KK 2016-03-28 05:48:55 Re: BUG #14047: Problem while initdb - 0xC000001D
Previous Message Robert Haas 2016-03-27 20:31:49 Re: BUG #14047: Problem while initdb - 0xC000001D