public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Vladimir Ryabtsev <[email protected]>
To: [email protected]
Subject: Re: Array of tuples as a parameter, with type casts
Date: Sat, 30 Nov 2019 07:15:25 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMqTPqntVMh1sk6V2GbwqX8ti7RtN2wQLVS-tYfVuVh8qWc4Tw@mail.gmail.com>
References: <CAMqTPqntVMh1sk6V2GbwqX8ti7RtN2wQLVS-tYfVuVh8qWc4Tw@mail.gmail.com>
On 11/29/19 8:30 PM, Vladimir Ryabtsev wrote:
> I have a query like this:
>
> query = '''
> insert into t
> select * from unnest (%s)
> as t1(c1 timestamp, c2 int)
> '''
>
> The reason of the approach is obviously reducing the number of server
> roundtrips when inserting many rows.
> Usage:
>
> from datetime import datetime
> import psycopg2
> db =
> psycopg2.connect('postgres://postgres:******@host/postgres?sslmode=prefer')
> cur = db.cursor()
> cur.execute(query, ([(datetime.now(), 1), (datetime.now(), 2)],))
> db.commit()
> db.close()
>
> Recently they needed to extend the column set by a text and a bigint
> columns. Neither of them works:
>
> from datetime import datetime
> import psycopg2
> query = '''insert into t
> select * from unnest (%s)
> as t1(c1 timestamp, c2 int, c3 text, c4 bigint)
> '''
> db = psycopg2.connect('postgres://postgres@localhost/postgres')
> cur = db.cursor()
> cur.execute(query, ([(datetime.now(), 1, 'abc', 100), (datetime.now(),
> 2, 'xyz', 200)],))
> db.commit()
> db.close()
>
> It throws:
> psycopg2.errors.DatatypeMismatch: function return row and
> query-specified return row do not match
> DETAIL: Returned type unknown at ordinal position 3, but query expects
> text.
>
> The problem is that the library sends the following request to the DBMS:
>
> insert into t
> select * from unnest (ARRAY[('2019-11-29T19:48:19.683310'::timestamp, 1,
> 'abc', 100),('2019-11-29T19:48:19.683320'::timestamp, 2, 'xyz', 200)])
> as t1(c1 timestamp, c2 int, c3 text, c4 bigint)
>
> For whatever reason it fails with the above error, but OK, it is related
> to Postgres, not to psycopg2.
> I can make it work by specifying type casts for text and bigint columns:
>
> insert into t
> select * from unnest (ARRAY[('2019-11-29T19:48:19.683310'::timestamp, 1,
> 'abc'::text, 100::bigint),('2019-11-29T19:48:19.683320'::timestamp, 2,
> 'xyz'::text, 200::bigint)])
> as t1(c1 timestamp, c2 int, c3 text, c4 bigint);
On a hunch try changing:
'''insert into t
select * from unnest (%s)
as t1(c1 timestamp, c2 int, c3 text, c4 bigint)'''
to
'''insert into t
select * from unnest (%s)
as t1(c1 timestamp, c2 int, c3 varchar, c4 bigint)'''
>
> But I need a way to make the psycopg2 module to do that. Another
> workaround would be creation a row type for the desired set of columns
> and casting %s to this type[], but I would not like to create additional
> objects in the database because it is pretty much out of my control.
>
> Your help is very appreciated.
>
> P.S. I am aware of other solutions such as execute_batch(),
> execute_values(), etc. Take this question as a theoretical one, I just
> want to understand if user is able to control this particular aspect of
> the module.
>
> psycopg2-binary==2.8.4
> Postgres ~ any
--
Adrian Klaver
[email protected]
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Array of tuples as a parameter, with type casts
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox