Subject: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?

From: Samuel Marks <[email-masked]>
Date: Mon, 20 Feb 2023 23:34:19 -0500

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>

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 20 Feb 2023 21:18:54 -0800

On 2/20/23 20:34, Samuel Marks wrote:
> How do I insert into a table with a `json[]` column using the `COPY 
> FROM` syntax?

Again why do you want to use json[]?

What possible use is that, when you have json and jsonb types available?

> <https://gist.github.com/SamuelMarks/fec744a620e2abd0257671aa6f2a96b4>
> 
> Error:
> https://stackoverflow.com/q/75511919 <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>

-- 
Adrian Klaver
[email-masked]

---

From: Aryeh Leib Taurog <[email-masked]>
Date: Tue, 21 Feb 2023 10:55:37 +0200

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

---

From: Daniele Varrazzo <[email-masked]>
Date: Tue, 21 Feb 2023 09:57:05 +0000

On Tue, 21 Feb 2023 at 05:34, Samuel Marks <[email-masked]> wrote:
>
> How do I insert into a table with a `json[]` column using the `COPY FROM` syntax?

you can just use psycopg 3 and pass it the records as tuples with the
objects you want to copy. The values of your json[] fields can be a
list of `Json` wrappers.

https://www.psycopg.org/psycopg3/docs/basic/copy.html#writing-data-row-by-row
https://www.psycopg.org/psycopg3/docs/basic/adapt.html#json-adaptation

If you want to know the escaping rules, they are in the Postgres documentation:

https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT

-- Daniele