public inbox for [email protected]
help / color / mirror / Atom feedArray of tuples as a parameter, with type casts
9+ messages / 4 participants
[nested] [flat]
* Array of tuples as a parameter, with type casts
@ 2019-11-30 04:30 Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-05 14:41 ` RE: Array of tuples as a parameter, with type casts David Raymond <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Vladimir Ryabtsev @ 2019-11-30 04:30 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
@ 2019-11-30 15:15 ` Adrian Klaver <[email protected]>
2019-12-04 00:00 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2019-11-30 15:15 UTC (permalink / raw)
To: Vladimir Ryabtsev <[email protected]>; [email protected]
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]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
@ 2019-12-04 00:00 ` Vladimir Ryabtsev <[email protected]>
2019-12-04 18:44 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Vladimir Ryabtsev @ 2019-12-04 00:00 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
It does not change much, the error message just says it now expects
'character varying'.
Anyway bigint problem is the next in the line.
--
Regards,
Vladimir
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 00:00 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
@ 2019-12-04 18:44 ` Adrian Klaver <[email protected]>
2019-12-04 18:47 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2019-12-04 18:44 UTC (permalink / raw)
To: Vladimir Ryabtsev <[email protected]>; +Cc: [email protected]
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 = "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}])])
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
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 00:00 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-12-04 18:44 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
@ 2019-12-04 18:47 ` Adrian Klaver <[email protected]>
2019-12-04 21:32 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2019-12-04 18:47 UTC (permalink / raw)
To: Vladimir Ryabtsev <[email protected]>; +Cc: [email protected]
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 = "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=json#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)
> ;
> 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
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 00:00 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-12-04 18:44 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 18:47 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
@ 2019-12-04 21:32 ` Vladimir Ryabtsev <[email protected]>
2019-12-04 22:50 ` Re: Array of tuples as a parameter, with type casts Daniele Varrazzo <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Vladimir Ryabtsev @ 2019-12-04 21:32 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
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
ср, 4 дек. 2019 г. в 10:47, Adrian Klaver <[email protected]>:
> 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 = "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=json#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)
> > ;
> > 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
> [email protected]
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 00:00 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-12-04 18:44 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 18:47 ` Re: Array of tuples as a parameter, with type casts Adrian Klaver <[email protected]>
2019-12-04 21:32 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
@ 2019-12-04 22:50 ` Daniele Varrazzo <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Daniele Varrazzo @ 2019-12-04 22:50 UTC (permalink / raw)
To: Vladimir Ryabtsev <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected]
On Wed, Dec 4, 2019 at 9:33 PM Vladimir Ryabtsev <[email protected]> wrote:
>
> 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...
You can have control adding casts next to the placeholders, such as
'%s::type'. For your case you can create a postgres composite type and
cast the placeholder to '%s::mytype[]'.
The problem in mapping Python lists to Postgres arrays is that in
Python the list is _the_ type, whereas in Postgres arrays are "array
of a type". In Python "type([1]) == type(['a'])"; in Postgres they are
not the same: "pg_typeof('{}'::text[]) != pg_typeof('{}'::int[])".
arrays are of homogeneous types different each other. There is no sane
or efficient algorithm to attach a Postgres cast looking at a generic
Python list. Do you look at the first argument? What if it's None
(which in Python is always the same NoneType, whereas in Postgres NULL
are typed)? What if the array is empty? The whole idea of attaching a
cast to the values, as psycopg does for dates - typing in
'2019-12-31'::date in the query, is actually a bad idea because that's
passing a sql snippet to the query rather than a value, so it couldn't
be used in server-side placeholder etc.
However you can use the same trick, subclassing some of your type to
make it adapt into a typed expression and solve types mismatches or
ambigiuties. If instead of a tuple you use a type of yours, e.g. a
namedtuple, you can make it adapt like super() does and appending a
'::type', and ARRAY[] will work because it will try to sniff the type
from the first argument (bets are off if it's None - no idea if
Postgres will look at the first non-NULL, or will barf, and if it does
what if your list is empty...). Or you can subclass the list instead
and make its spew a '::mytype[]', which would work even if it's empty
or contains None.
Using a '%s::mytype[]' *into the query* is the best option IMO:
usually if you are writing a certain query you know what types are
involved, whereas values might be coming from far away.
-- Daniele
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
@ 2019-12-05 14:41 ` David Raymond <[email protected]>
2019-12-05 17:43 ` Re: Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: David Raymond @ 2019-12-05 14:41 UTC (permalink / raw)
To: [email protected] <[email protected]>
“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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Array of tuples as a parameter, with type casts
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-12-05 14:41 ` RE: Array of tuples as a parameter, with type casts David Raymond <[email protected]>
@ 2019-12-05 17:43 ` Vladimir Ryabtsev <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Vladimir Ryabtsev @ 2019-12-05 17:43 UTC (permalink / raw)
To: David Raymond <[email protected]>; +Cc: [email protected] <[email protected]>
> Isn’t every table its own type? So could you do this?
Wow, you are right! I completely forgot about it!
I even don't need "as t(...)" column list.
Thanks!
--
Regards
Vladimir
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-12-05 17:43 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-30 04:30 Array of tuples as a parameter, with type casts Vladimir Ryabtsev <[email protected]>
2019-11-30 15:15 ` Adrian Klaver <[email protected]>
2019-12-04 00:00 ` Vladimir Ryabtsev <[email protected]>
2019-12-04 18:44 ` Adrian Klaver <[email protected]>
2019-12-04 18:47 ` Adrian Klaver <[email protected]>
2019-12-04 21:32 ` Vladimir Ryabtsev <[email protected]>
2019-12-04 22:50 ` Daniele Varrazzo <[email protected]>
2019-12-05 14:41 ` David Raymond <[email protected]>
2019-12-05 17:43 ` Vladimir Ryabtsev <[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