如何在PostgreSQL中从file_path字段提取唯一文件扩展名？

问题分析 #

你需要从pgsql_table_files表的file_path字段（URL格式）中提取文件扩展名，生成带行号的唯一扩展名列表。现有查询能提取扩展名，但未处理无扩展名的场景，也没实现去重和行号需求。

优化后的查询语句 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY extension) AS row_num,
    extension
FROM (
    SELECT DISTINCT
        CASE 
            WHEN strpos(reverse(file_path), '.') > 0 THEN reverse(substring(reverse(file_path) FROM 1 FOR strpos(reverse(file_path), '.') - 1))
            ELSE '无扩展名' -- 无扩展名时可自定义返回内容，也可设为NULL
        END AS extension
    FROM pgsql_table_files
    WHERE file_path IS NOT NULL -- 排除空路径记录
) AS ext_list
-- 可选：如果不需要显示无扩展名的记录，可添加下面的过滤条件
-- WHERE extension != '无扩展名';

语句说明 #

• 兼容无扩展名场景：通过CASE判断路径中是否包含.，避免无扩展名时strpos返回0导致substring参数为负数报错。
• 去重处理：内层查询用DISTINCT关键字获取唯一的扩展名值。
• 行号生成：外层用ROW_NUMBER() OVER (ORDER BY extension)生成按扩展名排序的行号，若需要按其他规则排序，可修改ORDER BY后的字段（比如按扩展名出现次数排序）。
• 空值过滤：通过WHERE file_path IS NOT NULL排除空路径的无效记录，可根据实际需求调整。

补充说明 #

如果URL路径包含多个.（例如https://example.com/file.tar.gz），上述语句会提取最后一个.后的内容（即gz），这符合常规的文件扩展名定义。若需要提取第一个.后的内容，可调整strpos的使用逻辑。

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