PostgreSQL常量列关联查询unknown转text报错原因及版本差异问询

问题1答复 #

这个报错是旧版本PostgreSQL本身的类型推断规则限制，不是sqlfiddle平台的问题。
PostgreSQL早期版本中，未指定类型的字符串字面量会被标记为unknown类型，且没有内置的隐式转换规则将unknown直接转换为text/varchar类型参与比较运算；而数字字面量默认会被优先推断为integer类型，所以第一条用数字常量的查询可以正常执行。
你本地使用的13.3属于较新版本，PostgreSQL从12版本开始优化了常量类型推断逻辑：当字符串字面量后续需要和字符类型字段做比较、关联运算时，会自动将unknown类型的常量推断为text类型，因此不会再触发这个报错；sqlfiddle上运行时使用的是更低版本的PostgreSQL，才会出现报错。

问题2答复 #

这个情况早期属于PostgreSQL的设计特性，不属于Bug。
SQL标准没有强制规定无上下文的常量默认类型，不同数据库的实现逻辑有差异：Oracle、SQLite会默认将字符串常量推断为字符类型，而早期PostgreSQL的设计就是将无类型的字符串常量标记为unknown，需要上下文有明确的类型提示才能完成转换，否则运算规则视为未定义。
后续新版本对该逻辑的优化，也是为了对齐通用使用习惯、降低用户的使用成本。

如果需要兼容旧版本PostgreSQL，只需要给字符串常量显式指定类型即可，示例如下：

with base (x) as (select 'a'::varchar as x from table1)
select table1.name from base, table1 where table1.name = base.x;

内容的提问来源于stack exchange，提问作者SBM