Re: copying json data and backslashes
> > On Tuesday, November 22, 2022 at 10:16:11 AM EST, Erik Wienhold wrote: > > > > On 22/11/2022 15:23 CET Alastair McKinley 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 > > [1] > https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.9.2 > [2] https://www.json.org/json-en.html > > -- > Erik
Re: copying json data and backslashes
On Tuesday, November 22, 2022 at 01:16:02 PM EST, Peter J. Holzer
wrote:
> On 2022-11-22 17:39:04 +, Alastair McKinley wrote:
> > > \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.
> >
> > 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.
>
Yes, Alastair, Peter said what I would have...
> You may have to hit several Keys[1] on your keyboard, but Ctrl-C is a
> single character, just like Shift-C is (the former has code 0003, the
> latter 0043).
>
> On Unix-like systems you can usually type the control characters by
> typing Ctrl-V first:
>
> At the psql prompt, type
> select ascii('
> then hit V while holding the ctrl key
> then hit C while holding the ctrl key
> The terminal should display that as ^C
> then complete the line with
> ');
> so that it looks like
> select ascii('^C');
> and hit return:
>
>
> [1] There are usually four Ctrl-Characters which need only a single
> key: Ctrl-I (TAB), Ctrl-M (CR), Ctrl-[ (ESC) and Ctrl-H (BS) or Ctrl-?
> (DEL).
>
> (On Unix systems CR is normally translated to LF, on Windows to CRLF)
>
Re: [EXT EMAIL] Re: First Time Starting Up PostgreSQL and Having Problems
Does the file have any \015's in it? On Wednesday, June 19, 2019, 7:03:25 PM EDT, Andrew Gierth wrote: > "Rob" == Rob Sargent writes: >>> local all all trust Rob> That line has four values and the header has 5. That's standard for "local" lines, which lack an ADDRESS field. -- Andrew (irc:RhodiumToad)
Detecting mis-planning of repeated application of a projection step
The 13.4 release notes document the following: > Fix mis-planning of repeated application of a projection step (Tom Lane) > > The planner could create an incorrect plan in cases where two > ProjectionPaths were stacked on top of each other. The only known > way to trigger that situation involves parallel sort operations, > but there may be other instances. The result would be crashes or > incorrect query results. Disclosure of server memory contents is also > possible. (CVE-2021-3677) > Is there a way to detect if this is happening and could this problem cause very bad, but successful, plans to be created? We are seeing 13.3 create horrible plans for some queries, where 13.4 creates great plans for the same queries. PJ
