PGSQL查询求助：去除字段末尾特定字符并关联查询报错解决

PostgreSQL的TRIM(trailing '-' FROM 字段)语法本身是正确的，报错大概率是以下几种原因导致，对应解决方案如下：

1. 字段存在NULL值 #

如果COLLECTION_ID或RESOURCE_ID字段有NULL值，TRIM(NULL)会返回NULL，而SQL中NULL = NULL不成立，会导致关联失败或语法问题。可以用COALESCE把NULL转为空字符串再处理：

SELECT 
    Resource.NAME COMPUTER_NAME, CollnToResources.remarks_en COLLECTION_REMARKS 
FROM Collection 
INNER JOIN CollnToResources 
    ON TRIM(trailing '-' FROM COALESCE(Collection.COLLECTION_ID, '')) = CollnToResources.COLLECTION_ID
INNER JOIN Resource 
    ON TRIM(trailing '-' FROM COALESCE(CollnToResources.RESOURCE_ID, '')) = Resource.RESOURCE_ID
WHERE Collection.COLLECTION_NAME IN ('OS_Installation_Date_v2', 'Linux_OS_Installation_Date')

2. 关联字段数据类型不匹配 #

如果关联的两个字段类型不一致（比如Collection.COLLECTION_ID是带'-'的字符串，CollnToResources.COLLECTION_ID是整数），直接用trim后的字符串和整数比较会报错。需要转换类型：

• 普通转换（确保trim后字符串可转为目标类型）：

SELECT 
    Resource.NAME COMPUTER_NAME, CollnToResources.remarks_en COLLECTION_REMARKS 
FROM Collection 
INNER JOIN CollnToResources 
    ON CAST(TRIM(trailing '-' FROM Collection.COLLECTION_ID) AS INT) = CollnToResources.COLLECTION_ID
INNER JOIN Resource 
    ON CAST(TRIM(trailing '-' FROM CollnToResources.RESOURCE_ID) AS INT) = Resource.RESOURCE_ID
WHERE Collection.COLLECTION_NAME IN ('OS_Installation_Date_v2', 'Linux_OS_Installation_Date')

• 容错转换（PostgreSQL 12+支持，转换失败返回NULL）：

SELECT 
    Resource.NAME COMPUTER_NAME, CollnToResources.remarks_en COLLECTION_REMARKS 
FROM Collection 
INNER JOIN CollnToResources 
    ON TRY_CAST(TRIM(trailing '-' FROM Collection.COLLECTION_ID) AS INT) = CollnToResources.COLLECTION_ID
INNER JOIN Resource 
    ON TRY_CAST(TRIM(trailing '-' FROM CollnToResources.RESOURCE_ID) AS INT) = Resource.RESOURCE_ID
WHERE Collection.COLLECTION_NAME IN ('OS_Installation_Date_v2', 'Linux_OS_Installation_Date')

3. SQL中存在全角空格或特殊字符 #

你的SQL代码里的缩进看起来有全角空格（比如SELECT后的空格），PostgreSQL无法识别全角空格，会触发语法错误。把所有全角空格替换为半角空格即可：

SELECT 
    Resource.NAME COMPUTER_NAME, CollnToResources.remarks_en COLLECTION_REMARKS 
FROM Collection 
INNER JOIN CollnToResources ON TRIM(trailing '-' FROM Collection.COLLECTION_ID) = CollnToResources.COLLECTION_ID 
INNER JOIN Resource ON TRIM(trailing '-' FROM CollnToResources.RESOURCE_ID) = Resource.RESOURCE_ID 
WHERE Collection.COLLECTION_NAME = 'OS_Installation_Date_v2' 
OR Collection.COLLECTION_NAME = 'Linux_OS_Installation_Date'

额外优化 #

把WHERE子句中的OR替换为IN，代码更简洁且性能更优：

WHERE Collection.COLLECTION_NAME IN ('OS_Installation_Date_v2', 'Linux_OS_Installation_Date')

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