Failed to find conversion function from unknown to text

In one of my select statements I've got the following error:

ERROR: failed to find conversion function from unknown to text ********** Error ********** ERROR: failed to find conversion function from unknown to text SQL state: XX000 

This was easy to fix using cast, but I do not fully understand why it happened. I will ilustrate my confusion with two simple statements.

This one is OK:

select 'text' union all select 'text'; 

This will return error:

with t as (select 'text') select * from t union all select 'text' 

I know I can fix it easily:

with t as (select 'text'::text) select * from t union all select 'text' 

Why does the conversion fail in the second example? Is there some logic I do not understand or this will be fixed in future version of PostgreSQL?

PostgreSQL 9.1.9

The same behavior on PostgreSQL 9.2.4 (SQL Fiddle)

1 Answer

Postgres is happy, if it can detect types of untyped constants from the context. But when any context is not possible, and when query is little bit more complex than trivial, then this mechanism fails. These rules are specific for any SELECT clause, and some are stricter, some not. If I can say, then older routines are more tolerant (due higher compatibility with Oracle and less negative impact on beginners), modern are less tolerant (due higher safety to type errors).

There was some proposals try to work with any unknown literal constant like text constant, but was rejected for more reasons. So I don't expect significant changes in this area. This issue is usually related to synthetic tests - and less to real queries, where types are deduced from column types.