PostgreSQL含空字符串的VARCHAR列按数值排序，空串视为0的方法

问题描述 #

我有一个PostgreSQL的VARCHAR类型列，该列仅包含数字或空字符串。我希望按数值对该列进行排序，但在将列转换为float类型时，出现如下错误：

ERROR: invalid input syntax for type double precision: ""

请问是否可以实现该排序需求，并将空字符串视为0？以下是我执行报错的查询语句：

SELECT C.content
FROM row R 
LEFT JOIN cell C ON C.row_id = R.row_id 
WHERE R.database_id = 'd1c39d3a-0205-4ee3-b0e3-89eda54c8ad2' 
AND C.column_id = '57833374-8b2f-43f3-bdf5-369efcfedeed'
ORDER BY cast(C.content as float)

解决方案 #

可以实现，核心是先把空字符串转成NULL，再将NULL替换为0后转换为数值类型排序。修改后的查询语句如下：

SELECT C.content
FROM row R 
LEFT JOIN cell C ON C.row_id = R.row_id 
WHERE R.database_id = 'd1c39d3a-0205-4ee3-b0e3-89eda54c8ad2' 
AND C.column_id = '57833374-8b2f-43f3-bdf5-369efcfedeed'
ORDER BY COALESCE(CAST(NULLIF(C.content, '') AS float), 0)

语句说明 #

• NULLIF(C.content, '')：将空字符串""转换为NULL值，避免直接转换float时报错
• CAST( ... AS float)：把非空的数字字符串转换为float类型
• COALESCE( ... , 0)：将转换后得到的NULL值替换为0，让空字符串按0参与排序

如果后续列中出现其他非数字异常值，PostgreSQL 12及以上版本可以使用SAFE_CAST替代CAST，避免转换报错：

ORDER BY COALESCE(SAFE_CAST(NULLIF(C.content, '') AS float), 0)

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