找不到从未知到文本的转换功能 [英] 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

使用 cast 可以轻松解决此问题，但是我不完全理解为什么会这样。我将用两个简单的语句来说明我的困惑。

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.

这是可以的：

select 'text'
union all
select 'text';

这将返回错误：

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'

在第二个示例中为什么转换失败？有一些我不理解的逻辑，还是会在将来的Pos​​tgreSQL版本中解决？

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

PostgreSQL 9.1.9

在PostgreSQL 9.2.4上的行为相同（ SQL Fiddle ）

The same behavior on PostgreSQL 9.2.4 (SQL Fiddle)

推荐答案

Postgres很高兴，如果它可以从上下文中检测出未类型化常量的类型。但是，当不可能有任何上下文，并且查询比平凡的查询要复杂得多时，该机制就会失败。这些规则特定于任何SELECT子句，有些更严格，有些则不严格。如果我能说的话，那么较旧的例程更具容忍性（由于与Oracle的兼容性更高，并且对初学者的负面影响较小），现代的容忍性较差（由于键入错误的安全性更高）。

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.

这篇关于找不到从未知到文本的转换功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！