public inbox for [email protected]  

help / color / mirror / Atom feed

From: Samuel Marks <[email protected]>
To: [email protected]
Subject: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
Date: Mon, 20 Feb 2023 23:34:19 -0500
Message-ID: <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com> (raw)

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;

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
  In-Reply-To: <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox