postgresql 无法找到从未知到文本的转换函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18073901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Failed to find conversion function from unknown to text
提问by Tomas Greif
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.
使用 很容易解决这个问题cast，但我不完全理解为什么会发生这种情况。我将用两个简单的陈述来说明我的困惑。
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 的未来版本中修复？
PostgreSQL 9.1.9
PostgreSQL 9.1.9
The same behavior on PostgreSQL 9.2.4 (SQL Fiddle)
PostgreSQL 9.2.4 ( SQL Fiddle)上的相同行为
采纳答案by Pavel Stehule
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).
Postgres 很高兴，如果它可以从上下文中检测到无类型常量的类型。但是当任何上下文都不可能时，并且当查询比琐碎更复杂一点时，这种机制就会失败。这些规则特定于任何 SELECT 子句，有些更严格，有些则不然。如果我可以说，那么旧的例程更宽容（由于与 Oracle 的兼容性更高，对初学者的负面影响更小），现代的宽容度更低（由于对键入错误的安全性更高）。
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.
有一些提议尝试使用任何未知的文字常量，如文本常量，但由于更多原因被拒绝。所以我不认为这方面会发生重大变化。这个问题通常与综合测试有关 - 而与真正的查询无关，其中类型是从列类型推导出来的。
