Re: 'text' instead of 'unknown' in Postgres 10

Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> writes:
> testing with psycopg2 against Postgres 10 I've found a difference in
> behaviour regarding literals, which are returned as text instead of
> unknown. ...
> Is this behaviour here to stay? Is there documentation for this change?

Yup, see
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e7c4bb0049732ece651d993d03bb6772e5d281a

The expectation is that clients will never see "unknown" output columns
anymore.

> 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.

I think you need to rethink that anyway, because in the old code,
whether such a value came back as text or unknown was dependent on
context, for example

regression=# select pg_typeof(x) from (select '' as x) ss;
pg_typeof
-----------
unknown
(1 row)

regression=# select pg_typeof(x) from (select distinct '' as x) ss;
pg_typeof
-----------
text
(1 row)

HEAD yields "text" for both of those cases, which seems a much saner
behavior to me.

I don't have enough context to suggest a better definition for psycopg
... but maybe you could pay some attention to the Python type of the value
you're handed?

> It seems
> this behaviour cannot be maintained on PG 10 and instead users need to
> specify some form of cast for their placeholder.

Well, no version of PG has ever allowed this without a cast:

regression=# select array[];
ERROR: cannot determine type of empty array

so I'm not sure it's inconsistent for the same restriction to apply in the
case you're describing. I'm also unclear on why you are emphasizing the
point of the array being empty, because '{1,2,3}'::unknown would have the
same behavior.

regards, tom lane