Date: Tue, 21 Feb 2023 10:55:37 +0200
From: Aryeh Leib Taurog <aryehleib@aryehleib.com>
To: psycopg@lists.postgresql.org
Subject: Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
Message-ID: <Y/SHCUsNSQTR4TKr@xi.aryehleib.com>
References: 
 <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Y/SHCUsNSQTR4TKr%40xi.aryehleib.com>
Precedence: bulk

As Adrian points out, it is a bit of a strange use case, since the 
jsonb itself could be an array.  If you really want to do this, 
however, have a look at pgcopy: https://pgcopy.readthedocs.io/

pgcopy makes COPY with psycopg2 easy, and supports jsonb[]
(Note: I am the author)

If you want to roll your own, feel free to have a look under the hood


On Mon, Feb 20, 2023 at 11:34:19PM -0500, Samuel Marks wrote:
> How do I insert into a table with a `json[]` column using the `COPY FROM`
> syntax?
> 
> Attempt:
> https://gist.github.com/SamuelMarks/fec744a620e2abd0257671aa6f2a96b4
> 
> Error:
> psycopg2.errors.InvalidTextRepresentation: malformed array literal:
> "{"jj":null,"text":"bop"}"
> 
> Or when I try to json.dump the dict twice I get:
> psycopg2.errors.InvalidTextRepresentation: malformed array literal:
> ""{"jj":null,"text":"bop"}"" DETAIL:  Array value must start with "{" or
> dimension information. CONTEXT:  COPY my_table, line 1, column
> json_arr_col: ""{"jj":null,"text":"bop"}""
> 
> I'm using your `copy_expert` function. PS: Also asked on
> https://stackoverflow.com/q/75511919
> 
> Am I meant to represent the lists with braces rather than square brackets?
> - Or should I be using some internal psycopg function as opposed to my
> hacked together `parse_col` function?
> 
> Thanks,
> 
> Samuel Marks
> Charity <https://sydneyscientific.org> | consultancy <https://offscale.io>
> | open-source <https://github.com/offscale> | LinkedIn
> <https://linkedin.com/in/samuelmarks>