public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: Vladimir Ryabtsev <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Array of tuples as a parameter, with type casts
Date: Wed, 4 Dec 2019 22:50:11 +0000
Message-ID: <CA+mi_8Z0MF6HXBCtpu5GqYxj4WAJOaex-92pBxXRE8J7SXcfbA@mail.gmail.com> (raw)
In-Reply-To: <CAMqTPqmZhY0aZhuusN6HUVyLtuZ2E6PJrt6L045XijV6w2xcEg@mail.gmail.com>
References: <CAMqTPqntVMh1sk6V2GbwqX8ti7RtN2wQLVS-tYfVuVh8qWc4Tw@mail.gmail.com>
	<[email protected]>
	<CAMqTPqk_d9kcuVrEwnu7dY5XKSh7BQQwvUBpKNciTTrhcxnORw@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAMqTPqmZhY0aZhuusN6HUVyLtuZ2E6PJrt6L045XijV6w2xcEg@mail.gmail.com>

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

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], [email protected]
  Subject: Re: Array of tuples as a parameter, with type casts
  In-Reply-To: <CA+mi_8Z0MF6HXBCtpu5GqYxj4WAJOaex-92pBxXRE8J7SXcfbA@mail.gmail.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