Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iccGd-0007rK-4L for psycopg@arkaria.postgresql.org; Wed, 04 Dec 2019 21:33:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iccGb-0000n8-OG for psycopg@arkaria.postgresql.org; Wed, 04 Dec 2019 21:33:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iccGb-0000n1-Ah for psycopg@lists.postgresql.org; Wed, 04 Dec 2019 21:33:01 +0000 Received: from mail-ua1-x934.google.com ([2607:f8b0:4864:20::934]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iccGX-0004TU-9n for psycopg@lists.postgresql.org; Wed, 04 Dec 2019 21:32:59 +0000 Received: by mail-ua1-x934.google.com with SMTP id 59so378851uap.12 for ; Wed, 04 Dec 2019 13:32:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=2yaB4EFe0mGtBZx/CX9MpdqUvHBukySXEyvofGdfcPo=; b=MzKzDRGmXeJ9vBTXc8sSKQD5J5t7FvDKL6VM2RFCGzP3A8Mi2eYbTLA0XHwT7l/g4/ ikV4LCigX4u/XKi7GmXTKlhOth7o/7nmTrmL5hnSGibziHHb+8+wB9IkX/YNye1pFQx2 XMG9ce1Eai3ZBca1YmD9jnHdpYcZr5PrbvHguGhwnR2DRAzesfMQ92+MpvLZkolILNf/ Dp9MWibezzqH7OxmheqRbILepMeTm/QmusCG9bAUcnYzCB36QBEbMmXkcqIMlbJSwFNR 5wnNw2YdiqDSZqx5o3rpRu0lHsl8zhXe92LGPV3oU5sKP1Ik/CZ7ToT6fU3vMvfBiL9B acnA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=2yaB4EFe0mGtBZx/CX9MpdqUvHBukySXEyvofGdfcPo=; b=ZBvLnutEMuHu7ZRvWPAzjHWHX67I+/PBhM9+Uoh364XWCP3qjsDLw5f8iyEP6KtgjD AKFz3JjJMYU/xOthWdwQm4AsSdU2RkysdsWEQtJD9gmZ96rXIofpIpAOoEDimgNI7zzJ CZQnF2/YuF+sIeG/McTXo7NDKj87BHc3W1MEGVvcIv76jlo90FH456+EP7h9RwRf4czn /qTZR+9OIKV+wWAx9XtjeNpIrosZVjvZB/AwWVbhZcSK33AD0j6ldDDJxm/LCRWhgSq+ KH/wUV1N6iU8eTVzjj1cvvowgp7zLQok1bPycj6DjiTi+BoqqK/cZBk25cPS1BtSgl5u +8Jg== X-Gm-Message-State: APjAAAXYxrzwJDHAAzipEbuf8A4m/zzUuhNVEqvc7DlE9Hr0j/T6rbCx O+k2unEyLwx7TiBpN9H7ECQ2bDj3XxhRJpaofII= X-Google-Smtp-Source: APXvYqyXzwo2iZHwfqL8kvSowCcvnnmQVTUjVT5OBqJD9W0gG41jCnV+DIOv0Tl5cguofVtDwd/tU9guoYMsA4fmREc= X-Received: by 2002:ab0:4ad4:: with SMTP id t20mr4418897uae.120.1575495175281; Wed, 04 Dec 2019 13:32:55 -0800 (PST) MIME-Version: 1.0 References: <25c3598a-698f-8d0e-3659-791253b903ec@aklaver.com> <55fa8796-a91b-6560-604c-83dd77b17980@aklaver.com> In-Reply-To: <55fa8796-a91b-6560-604c-83dd77b17980@aklaver.com> From: Vladimir Ryabtsev Date: Wed, 4 Dec 2019 13:32:44 -0800 Message-ID: Subject: Re: Array of tuples as a parameter, with type casts To: Adrian Klaver Cc: psycopg@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000044a2880598e78eaa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000044a2880598e78eaa Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes, I know it can be done with Json, too. I was particularly interested if it can be controlled in psycopg2 module, because the serialization to ARRAY[...] string is performed be the module and in this particular case explicit specification of types is required, but it seems like the module does not provide any control over it... Thanks anyway! -- Regards, Vladimir =D1=81=D1=80, 4 =D0=B4=D0=B5=D0=BA. 2019 =D0=B3. =D0=B2 10:47, Adrian Klave= r : > On 12/4/19 10:44 AM, Adrian Klaver wrote: > > On 12/3/19 4:00 PM, Vladimir Ryabtsev wrote: > >> It does not change much, the error message just says it now expects > >> 'character varying'. > >> Anyway bigint problem is the next in the line. > > > > Yeah it is related to this: > > > > https://www.postgresql.org/docs/11/functions-array.html > > unnest(anyarray) setof anyelement > > > > Dealing with anyelement can be tricky. > > > > I tried out an alternate solution: > > > > json_query =3D "select * from json_to_recordset(%s) as t(a timestamp, b > > varchar, c bigint)" > > > > cur.execute(json_query,[Json([{'a': datetime.now().isoformat(), 'b': > > 'abc', 'c': 100},{'a': datetime.now().isoformat(), 'b': 'xyz', 'c': > > 200}])]) > > Should have added that Json comes from: > > from psycopg2.extras import Json > > http://initd.org/psycopg/docs/extras.html?highlight=3Djson#json-adaptatio= n > > > > > > Which results in: > > > > test_(postgres)# select * from json_to_recordset(E'[{"a": > > "2019-12-04T10:40:22.823910", "b": "abc", "c": 100}, {"a": > > "2019-12-04T10:40:22.823918", "b": "xyz", "c": 200}]') as t(a timestamp= , > > b varchar, c bigint) > > ; > > a | b | c > > ----------------------------+-----+----- > > 2019-12-04 10:40:22.82391 | abc | 100 > > 2019-12-04 10:40:22.823918 | xyz | 200 > > (2 rows) > > > >> > >> -- > >> Regards, > >> Vladimir > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --00000000000044a2880598e78eaa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes, I know it can be done with Json, too. I was particula= rly interested if it can be controlled in psycopg2 module, because the seri= alization to ARRAY[...] string is performed be the module and in this parti= cular case explicit specification of types is required, but it seems like t= he module does not provide any control over it...
Thanks anyway!
<= div>
--
Regards,
Vladimir

=
=D1=81=D1= =80, 4 =D0=B4=D0=B5=D0=BA. 2019 =D0=B3. =D0=B2 10:47, Adrian Klaver <adrian.klaver@aklaver.com>= :
On 12/4/19 10:= 44 AM, Adrian Klaver wrote:
> On 12/3/19 4:00 PM, Vladimir Ryabtsev wrote:
>> It does not change much, the error message just says it now expect= s
>> 'character varying'.
>> Anyway bigint problem is the next in the line.
>
> Yeah it is related to this:
>
> https://www.postgresql.org/docs/11/funct= ions-array.html
> unnest(anyarray)=C2=A0=C2=A0=C2=A0=C2=A0 setof anyelement
>
> Dealing with anyelement can be tricky.
>
> I tried out an alternate solution:
>
> json_query =3D "select * from json_to_recordset(%s) as t(a timest= amp, b
> varchar, c bigint)"
>
> cur.execute(json_query,[Json([{'a': datetime.now().isoformat()= , 'b':
> 'abc', 'c': 100},{'a': datetime.now().isoforma= t(), 'b': 'xyz', 'c':
> 200}])])

Should have added that Json comes from:

from psycopg2.extras import Json

http://initd.org/psycopg/d= ocs/extras.html?highlight=3Djson#json-adaptation


>
> Which results in:
>
> test_(postgres)# select * from json_to_recordset(E'[{"a"= :
> "2019-12-04T10:40:22.823910", "b": "abc"= , "c": 100}, {"a":
> "2019-12-04T10:40:22.823918", "b": "xyz"= , "c": 200}]') as t(a timestamp,
> b varchar, c bigint)
> ;
>=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 a=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 |=C2=A0 b=C2=A0 |=C2=A0 c
> ----------------------------+-----+-----
>=C2=A0 =C2=A02019-12-04 10:40:22.82391=C2=A0 | abc | 100
>=C2=A0 =C2=A02019-12-04 10:40:22.823918 | xyz | 200
> (2 rows)
>
>>
>> --
>> Regards,
>> Vladimir
>
>


--
Adrian Klaver
adrian.klave= r@aklaver.com
--00000000000044a2880598e78eaa--