| From: | Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> |
|---|---|
| To: | "pbj(at)cmicdo(dot)com" <pbj(at)cmicdo(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, Erik Wienhold <ewie(at)ewie(dot)name> |
| Subject: | Re: copying json data and backslashes |
| Date: | 2022-11-22 17:39:04 |
| Message-ID: | PAXPR02MB7600A3361EE070A23F226481E30D9@PAXPR02MB7600.eurprd02.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
> From: pbj(at)cmicdo(dot)com <pbj(at)cmicdo(dot)com>
> Sent: 22 November 2022 15:30
> To: Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>; Erik Wienhold <ewie(at)ewie(dot)name>
> Subject: Re: copying json data and backslashes
>
> >
> > On Tuesday, November 22, 2022 at 10:16:11 AM EST, Erik Wienhold <ewie(at)ewie(dot)name> wrote:
> >
> >
> > > On 22/11/2022 15:23 CET Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> wrote:
> > >
> > > Hi all,
> > >
> > > I have come across this apparently common issue COPY-ing json and wondering if
> > > there is potentially a better solution.
> > >
> > > I am copying data into a jsonb column originating from a 3rd party API. The
> > > data may have literal \r,\t,\n and also double backslashes.
> > >
> > > I discovered that I can cast this data to a jsonb value directly but I can't
> > > COPY the data without pre-processing.
> >
> >
> > > Is there any other solution with COPY that doesn't require manual
> > > implementation of search/replace to handle these edge cases?
> > > Why does ::jsonb work but COPY doesn't? It seems a bit inconsistent.
> >
> > COPY handles special backslash sequences[1]. The \r in your sample JSON,
> > although properly escaped according to JSON, is replaced with an actual
> > carriage return by COPY before casting to jsonb. The error results from JSON
> > prohibiting unescaped control characters in strings[2].
> >
> > You must double escape to pass those characters through COPY.
> >
> > See how COPY outputs backslash sequences:
> >
> > -- Actual carriage return:
> > copy (select e'\r') to stdout;
> > \r
> >
> > -- Backslash sequence for carriage return:
> > copy (select '\r') to stdout;
> >
> > \\r
>
> I have been able to get around this problem by using the following method:
>
> \copy footable from 'input.json' (format csv, escape '^B', delimieter '^C', quote '^E')
>
> where the control characters are the actual control char, not the caret-letter, and it requires no escaping escapes. I realize this won't work for all
> situations.
>
> PJ
Hi PJ,
Thanks for the suggestion, this is interesting to me to try but I am not quite sure how this works.
As far as I understand, escape/quote/delimiter have to be a single character, and CTRL-C etc. are multiple characters.
What way do you input each of the escape/quote/delimiter characters?
Best regards,
Alastair
>
> >
> > [1]
> > https://stream.boxwatch.ru/docs/current/sql-copy.html#id-1.9.3.55.9.2
> > [2] https://www.json.org/json-en.html
> >
> > --
> > Erik
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter J. Holzer | 2022-11-22 18:15:50 | Re: copying json data and backslashes |
| Previous Message | Adrian Klaver | 2022-11-22 16:57:57 | Re: system variable can be edited by all user? |