Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxV0N-0004EF-42 for pgsql-general@arkaria.postgresql.org; Tue, 22 Nov 2022 15:16:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxV0L-000273-NE for pgsql-general@arkaria.postgresql.org; Tue, 22 Nov 2022 15:16:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxV0L-00026u-DQ for pgsql-general@lists.postgresql.org; Tue, 22 Nov 2022 15:16:09 +0000 Received: from mout-u-204.mailbox.org ([80.241.59.204]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxV0H-0003GX-BW for pgsql-general@lists.postgresql.org; Tue, 22 Nov 2022 15:16:08 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [IPv6:2001:67c:2050:b231:465::1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4NGnrv300Dz9sRj; Tue, 22 Nov 2022 16:15:59 +0100 (CET) Date: Tue, 22 Nov 2022 16:15:57 +0100 (CET) From: Erik Wienhold To: Alastair McKinley , "pgsql-general@lists.postgresql.org" Message-ID: <1342743333.378397.1669130157980@office.mailbox.org> In-Reply-To: References: Subject: Re: copying json data and backslashes MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4NGnrv300Dz9sRj List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > 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. > > The example below illustrates my issue (only with \r, but the problem extends > to other \X combinations). > > > do $$ > > lines=[r'{"test" : "\r this data has a carriage return"}'] > > > > with open("/tmp/test1.json","w") as f: > > for line in lines: > > f.write(line.strip() + "\n") > > > > $$ language plpython3u; > > > > create temp table testing (data jsonb); > > > > -- this works > > insert into testing (data) > > select l::jsonb > > from pg_read_file('/tmp/test1.json') f, > > lateral regexp_split_to_table(f,'\n') l where l <> ''; > > > > -- fails > > copy testing (data) from '/tmp/test1.json'; > > > > -- works > > copy testing (data) from program $c$ sed -e 's/\\r/\\\\u000a/g' /tmp/test1.json $c$; > > > > 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 [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