找不到从未知到文本的转换函数

在我的一个精选语句中，我得到了以下错误:

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

这是很容易修复使用 cast，但我不完全理解为什么会发生这种情况。我将用两个简单的陈述来说明我的困惑。

这个没问题:

select 'text'
union all
select 'text';

这将返回错误:

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

我知道我可以很容易地修好它:

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

为什么在第二个例子中转换失败了？是否存在一些我不理解的逻辑，或者这将在 PostgreSQL 的未来版本中得到修复？

PostgreSQL 9.1.9

PostgreSQL 9.2.4(SQL Fiddle)上的相同行为

90754 次浏览
小开
最佳答案

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.


提交答案