Thread: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
How do I insert into a table with a `json[]` column using the `COPY FROM` syntax?
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"}""
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
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? > > Attempt: > https://gist.github.com/SamuelMarks/fec744a620e2abd0257671aa6f2a96b4 > <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 <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 adrian.klaver@aklaver.com
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>
On Tue, 21 Feb 2023 at 05:34, Samuel Marks <samuelmarks@gmail.com> 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