Feedback-ID: i76984098:Fastmail
Message-ID: <7c40b067-0bf6-82f9-4b88-9a95242d3811@aklaver.com>
Date: Mon, 20 Feb 2023 21:18:54 -0800
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.8.0
Subject: Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion?
Content-Language: en-US
To: Samuel Marks <samuelmarks@gmail.com>, psycopg@lists.postgresql.org
References: 
 <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CAMfPbcbOoC0k2CoSDo1jR-nh98N5qs_aU1T9hEXYpMfpQb-bZA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7c40b067-0bf6-82f9-4b88-9a95242d3811%40aklaver.com>
Precedence: bulk

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