Thread: Why do subselects in target lists behave differently wrt implicit casts?

This is a really trivial question, but I'm curious. Why do
subselects in target lists behave differently than simple queries?

Ex:

[lexus] create temporary table bar (key varchar(32) not null);
CREATE TABLE
[lexus] insert into bar select '';
INSERT 1319585 1
[lexus] insert into bar select (select '') as key;
ERROR:  failed to find conversion function from "unknown" to
character varying
[lexus] insert into bar select (select ''::text) as key;
INSERT 1319586 1

Just curious,

Mike Mascari

Mike Mascari <mascarm@mascari.com> writes:
> This is a really trivial question, but I'm curious. Why do
> subselects in target lists behave differently than simple queries?

> [lexus] insert into bar select '';
> INSERT 1319585 1
> [lexus] insert into bar select (select '') as key;
> ERROR:  failed to find conversion function from "unknown" to
> character varying

The undecorated literal constant starts out marked as type UNKNOWN.
There is a hack to reinterpret it as the type of the destination
column in an INSERT context.  The hack doesn't know anything about
looking inside subselects, though.

            regards, tom lane