psycopg 3 cursor.execute no longer accepts Python tuple binding

[David Burke]

This may be a bug or a missing feature of psycopg 3. If expected, it may be worth mentioning as a breaking change when using psycopg3.

The following Django code works with psycopg2. Notice the usage of a tuple with the "in" statement.

cursor.execute("select 1 where 1 in %s", ((1,),)

But in psycopg 3 it gives an error

django.db.utils.ProgrammingError: syntax error at or near "'{1}'"
LINE 1: select 1 where 1 in '{1}'::int2[]

A fix is to use ANY with a list. It must specifically be a list and not a tuple.

cursor.execute("select 1 where 1 = ANY(%s)", ([1],))

With a tuple, we get the error

django.db.utils.ProgrammingError: syntax error at or near "'(1)'"
LINE 1: select 1 where 1 in '(1)'

I would expect execute to treat a Python list and tuple the same when binding to a postgresql parameter. But this is not so.

[Mariusz Felisiak]

Replying to David Burke:

This may be a bug or a missing feature of psycopg 3. If expected, it may be worth mentioning as a breaking change when using psycopg3.

Thanks for the ticket. This is a backward incompatibility change explicitly stated in psycopg ​docs (there are other small ​caveats when using raw SQL statements). It's not something that we want/can change in Django itself. Moreover, it crashes with other backends so it's now more consistent.

We normally don't document backward incompatibility changes in database adapters, especially on a low-level of executing raw SQL statements. We don't want to copy psycopg 3 docs here. I think your ticket will be enough to raise awareness on this small inconvenience.

[Mariusz Felisiak <felisiak.mariusz@…>]

In ff9ceed3:

Refs #34434 -- Added note about breaking changes in psycopg version 3 to release notes.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 272dc386:

[4.2.x] Refs #34434 -- Added note about breaking changes in psycopg version 3 to release notes.

Backport of ff9ceed32b880733d94190742e45dbcf5927b161 from main