Puzzled by UNION with unknown types

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.

It seems contingent on whether the value is generated on the fly, but
I don't understand the mechanism or distinction. Any help
appreciated!

-- These work:

ag_consulting=> SELECT '1'::unknown UNION ALL SELECT '2'::unknown;

unknown
---------
1
2
(2 rows)

-- db_list is a table with one row:

ag_consulting=> SELECT '1'::unknown FROM db_list UNION ALL SELECT
'2'::unknown FROM db_list;
unknown
---------
1
2
(2 rows)

-- These don't work:

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

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 |

ag_consulting=> SELECT * FROM t1 UNION ALL SELECT * FROM t1;
ERROR: failed to find conversion function from unknown to text

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.