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.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
Ken Tanzer <ken.tanzer@gmail.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks Tom!
--
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.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.