Re: Array of tuples as a parameter, with type casts

On Wed, Dec 4, 2019 at 9:33 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote:
>
> Yes, I know it can be done with Json, too. I was particularly interested if it can be controlled in psycopg2 module,
becausethe serialization to ARRAY[...] string is performed be the module and in this particular case explicit
specificationof 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