testing with psycopg2 against Postgres 10 I've found a difference in behaviour regarding literals, which are returned as text instead of unknown. In previous versions:
In [2]: cnn = psycopg2.connect('') In [3]: cur = cnn.cursor() In [7]: cur.execute("select 'x'") In [9]: cur.description[0][1] Out[9]: 705
In pg10 master:
In [10]: cnn = psycopg2.connect('dbname=postgres host=localhost port=54310') In [11]: cur = cnn.cursor() In [12]: cur.execute("select 'x'") In [13]: cur.description[0][1] Out[13]: 25
what is somewhat surprising is that unknown seems promoted to text "on the way out" from a query; in previous versions both columns of this query would have been "unknown".
postgres=# select pg_typeof('x'), pg_typeof(foo) from (select 'x' as foo) x; pg_typeof | pg_typeof -----------+----------- unknown | text
Is this behaviour here to stay? Is there documentation for this change?
In psycopg '{}'::unknown is treated specially as an empty array and converted into an empty list, which allows empty lists to be passed to the server as arrays and returned back to python. Without the special case, empty lists behave differently from non-empty ones. It seems this behaviour cannot be maintained on PG 10 and instead users need to specify some form of cast for their placeholder. Previously this would have worked "as expected" and the 4th argument would have been an empty list:
