Re: Puzzled by UNION with unknown types

Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> Hi. I've solved my practical problems, and I know unknown types are
> just bad, but am still curious about why some of these cause errors,
> and others don't.

> ag_consulting=> SELECT * FROM (SELECT '1'::unknown) foo UNION ALL
> SELECT * FROM (SELECT '1'::unknown) bar;
> ERROR: failed to find conversion function from unknown to text

FWIW, this succeeds in 9.5 and up, as a result of more aggressive
application of the rule "coerce an unknown value to text if we cannot
find any other interpretation".

> ag_consulting=> CREATE TEMP TABLE t1 AS SELECT '1'::unknown; \d t1
> WARNING: column "unknown" has type "unknown"
> DETAIL: Proceeding with relation creation anyway.
> SELECT 1
> Table "pg_temp_2.t1"
> Column | Type | Modifiers
> ---------+---------+-----------
> unknown | unknown |

As of v10, this will produce a table with a column of type text,
not type unknown, again as a result of more aggressively forcing
unknown to be something else.

regards, tom lane