sql - 从 9.0 升级到 9.3 后，PostgreSQL 处理标识符是否区分大小写？

Question

我有一个在 Postgres 9.0 上运行良好的应用程序，但在升级到 9.3 后，一个巨大的 sql 语句停止工作。我的假设是 pg 行为从不区分大小写变为区分大小写。这是适用于 pg 9.0 的语句，但在 9.3 上会抛出错误:

> ERROR:  column tmplrole.name does not exist at character 35

查询:

select cftCE.f_resource as ceId, tmplRole.name as tRole, mdtk.meta_type_key as metaKey, md1.metadatum_value as metaValue, grp1.id as groupId 
from account acc1 
left outer join account_role accRole on acc1.f_principal=accRole.f_account  
left outer join roles r1 on accRole.f_role=r1.f_principal 
left outer join template_role tmplRole on r1.f_template_role=tmplRole.id 
left outer join groups grp1 on r1.f_group=grp1.id left outer join content_entry cftCE on grp1.f_content_entry=cftCE.f_resource 
left outer join metadatum md1 on cftCE.f_resource=md1.f_content_entry  
left outer join meta_type mdtk on md1.f_meta_type=mdtk.id  
inner join ( select f_content_entry as permCe from groups grp 
inner join ( select f_group as groupId from operation opr     
inner join ( select ppr.f_resource as cftce from PRINCIPAL_PERMISSION_RESOURCE ppr      
inner join ( select roles.f_principal  as userrole from roles, account_role, account      
where roles.f_principal=account_role.F_role and account_role.f_account=account.f_principal and account.f_principal=$1 )
AS roles_alias on ppr.f_principal = userrole      
inner join (select operation.f_resource  as viewCftOperation from Operation where Operation.F_TEMPLATE_OPERATION=$2 ) 
AS operation_alias on ppr.f_resource = viewCftOperation ) AS ppr_alias on opr.F_resource = cftce )
AS group_alias  on grp.ID = groupid ) 
AS content_entry_alias on cftCE.f_resource = permCe where acc1.f_principal=$3
and (mdtk.meta_type_key=$4 or mdtk.meta_type_key=$5 or mdtk.meta_type_key=$6) order by cftCE.date_created desc;

Answer 1

区分大小写的规则没有改变，但是 tablename.type 的处理在 PostgreSQL 9.1 中发生了变化。要注意的是 name 是一个 postgres 内置类型，用于目录。

这是 9.1 release notes 中的相关条目:

E.14.2.2. Casting
Disallow function-style and attribute-style data type casts for composite types (Tom Lane)

For example, disallow composite_value.text and text(composite_value). Unintentional uses of this syntax have frequently resulted in bug reports; although it was not a bug, it seems better to go back to rejecting such expressions. The CAST and :: syntaxes are still available for use when a cast of an entire composite value is actually intended.

我的猜测是您的表 template_role 在 9.0 中没有 name 列，并且正在解释 tmplRole.name因为 tmplRole::name 是一个在您的查询中未被注意到的错误。

另请参阅相关的 Is name a special keyword in PostgreSQL?