public inbox for [email protected]
help / color / mirror / Atom feedEscape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
4+ messages / 4 participants
[nested] [flat]
* Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
@ 2023-02-21 04:34 Samuel Marks <[email protected]>
0 siblings, 3 replies; 4+ messages in thread
From: Samuel Marks @ 2023-02-21 04:34 UTC (permalink / raw)
To: [email protected]
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
@ 2023-02-21 05:18 Adrian Klaver <[email protected]>
parent: Samuel Marks <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2023-02-21 05:18 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
@ 2023-02-21 08:55 Aryeh Leib Taurog <[email protected]>
parent: Samuel Marks <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Aryeh Leib Taurog @ 2023-02-21 08:55 UTC (permalink / raw)
To: [email protected]
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
@ 2023-02-21 09:57 Daniele Varrazzo <[email protected]>
parent: Samuel Marks <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Daniele Varrazzo @ 2023-02-21 09:57 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; +Cc: [email protected]
On Tue, 21 Feb 2023 at 05:34, Samuel Marks <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-02-21 09:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-21 04:34 Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion? Samuel Marks <[email protected]>
2023-02-21 05:18 ` Adrian Klaver <[email protected]>
2023-02-21 08:55 ` Aryeh Leib Taurog <[email protected]>
2023-02-21 09:57 ` Daniele Varrazzo <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox