Extended-query protocol placeholder types about psycopg HOT 4 CLOSED

Hello, thank you for looking into this.

Have you started from where I left? Just to know if this is one of the differences I had already tackled or if it was ahead.

The problem behind this specific cast is because text is - unexpectedly - passed as unknown, not as text, by psycopg3. The reason is explained here I didn't expect this failure though: I checked why, and it's likely because concat() arguments are defined as VARIADIC any in Postgres, not as text.

piro=# \df *.concat
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type 
------------+--------+------------------+---------------------+------
 pg_catalog | concat | text             | VARIADIC "any"      | func
(1 row)

Most of the time I found these differences I could solve them by making the Django backend create the placeholders with added cast: where the psycopg2 backend generates CONCAT(%s, %s) the psycopg3 one should generate CONCAT(%s::text, %s::text). See this commit for instance: dvarrazzo/django@215d2e3

From a cursory look at my commits in the django repos, things might have changed a bit in psycopg3 since (but not regards this topic: for instance I dropped cursor.callproc()). So if you find anything unexpected please let me know: I'm happy to give support for this Django driver and I appreciate your interest.

Cheers!

from psycopg.

Thank you for shedding the light on the library behaviour!

I started from dvarrazzo/django@12280d7, which looks like the very last commit in psycopg3 branch.

I created a little PR with proposed CONCAT(%s, %s) -> CONCAT(%s::text, %s::text) conversion: dvarrazzo/django#1. Hope it helps :)

Cheers!

from psycopg.

Shall we log transformed queries somehow? It would have saved me a lot of time in this case.

Note that you can find query and params as attributes on the cursor, if you need them.

from psycopg.

Shall we log transformed queries somehow? It would have saved me a lot of time in this case.

Note that you can find query and params as attributes on the cursor, if you need them.

Thanks for the tip!

from psycopg.