MIME-Version: 1.0
From: Samuel Marks <samuelmarks@gmail.com>
Date: Mon, 20 Feb 2023 23:34:19 -0500
Message-ID: 
 <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com>
Subject: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
To: psycopg@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b8d46905f52e4b00"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA%40mail.gmail.com>
Precedence: bulk

--000000000000b8d46905f52e4b00
Content-Type: text/plain; charset="UTF-8"

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>

--000000000000b8d46905f52e4b00
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>How do I insert into a table with a `json[]` column u=
sing the `COPY FROM` syntax?<br></div><div><br></div><div>Attempt: <a href=
=3D"https://gist.github.com/SamuelMarks/fec744a620e2abd0257671aa6f2a96b4">h=
ttps://gist.github.com/SamuelMarks/fec744a620e2abd0257671aa6f2a96b4</a></di=
v><div><br></div><div>Error:</div>psycopg2.errors.InvalidTextRepresentation=
: malformed array literal: &quot;{&quot;jj&quot;:null,&quot;text&quot;:&quo=
t;bop&quot;}&quot;<div><div dir=3D"ltr" class=3D"gmail_signature" data-smar=
tmail=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><div>=
Or when I try to json.dump the dict twice I get:<br>psycopg2.errors.Invalid=
TextRepresentation: malformed array literal: &quot;&quot;{&quot;jj&quot;:nu=
ll,&quot;text&quot;:&quot;bop&quot;}&quot;&quot; DETAIL: =C2=A0Array value =
must start with &quot;{&quot; or dimension information. CONTEXT: =C2=A0COPY=
 my_table, line 1, column json_arr_col: &quot;&quot;{&quot;jj&quot;:null,&q=
uot;text&quot;:&quot;bop&quot;}&quot;&quot;</div><div dir=3D"ltr"><br></div=
><div>I&#39;m using your `copy_expert` function. PS: Also asked on <a href=
=3D"https://stackoverflow.com/q/75511919">https://stackoverflow.com/q/75511=
919</a></div><div><br></div><div>Am I meant to represent the lists with bra=
ces rather than square brackets? - Or should I be using some internal psyco=
pg function as opposed to my hacked together `parse_col` function?</div><di=
v><br></div><div>Thanks,<br></div><div dir=3D"ltr"><br></div><div dir=3D"lt=
r">Samuel Marks<br></div><div><a href=3D"https://sydneyscientific.org" targ=
et=3D"_blank">Charity</a> | <a href=3D"https://offscale.io" target=3D"_blan=
k">consultancy</a> | <a href=3D"https://github.com/offscale" target=3D"_bla=
nk">open-source</a> | <a href=3D"https://linkedin.com/in/samuelmarks" targe=
t=3D"_blank">LinkedIn</a></div></div></div></div></div>

--000000000000b8d46905f52e4b00--