public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Raymond <[email protected]>
To: [email protected] <[email protected]>
Subject: RE: Array of tuples as a parameter, with type casts
Date: Thu, 5 Dec 2019 14:41:00 +0000
Message-ID: <VI1PR07MB60293B02275E16B616D95EC6875C0@VI1PR07MB6029.eurprd07.prod.outlook.com> (raw)
In-Reply-To: <CAMqTPqntVMh1sk6V2GbwqX8ti7RtN2wQLVS-tYfVuVh8qWc4Tw@mail.gmail.com>
References: <CAMqTPqntVMh1sk6V2GbwqX8ti7RtN2wQLVS-tYfVuVh8qWc4Tw@mail.gmail.com>

“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.”

Isn’t every table its own type? So could you do this?
inert into t
select * from unnest(%s::t[])...


https://www.postgresql.org/docs/current/rowtypes.html

Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. For example, had we said:

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

then the same inventory_item composite type shown above would come into being as a byproduct, and could be used just as above.
...

From: Vladimir Ryabtsev <[email protected]>
Sent: Friday, November 29, 2019 11:30 PM
To: [email protected]
Subject: Array of tuples as a parameter, with type casts

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);

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

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]
  Subject: RE: Array of tuples as a parameter, with type casts
  In-Reply-To: <VI1PR07MB60293B02275E16B616D95EC6875C0@VI1PR07MB6029.eurprd07.prod.outlook.com>

* 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