PostgreSQL 9.6获取数组长度报错，求文件名处理实现方法

问题原因 #

array_length函数需要两个参数：目标数组和指定维度（一维数组传1即可），你仅传入数组参数导致报错；length函数仅适用于字符串，无法直接处理数组类型，因此也会触发错误。

正确实现方法 #

1. 获取数组元素长度 #

针对一维数组，有两种简洁方式：

• 使用array_length（需指定维度）：

SELECT array_length(string_to_array('file.name.pdf', '.'), 1);
-- 返回结果：3

• 使用cardinality（PostgreSQL 9.4+支持，写法更简单）：

SELECT cardinality(string_to_array('file.name.pdf', '.'));
-- 返回结果：3

2. 获取文件扩展名（数组最后一个元素） #

利用PostgreSQL数组从1开始索引的特性，结合数组长度定位最后一个元素：

SELECT elements[array_length(elements, 1)] AS extension
FROM (SELECT string_to_array('file.name.pdf', '.') AS elements) t;
-- 返回结果：pdf

用cardinality简化写法：

SELECT elements[cardinality(elements)] AS extension
FROM (SELECT string_to_array('file.name.pdf', '.') AS elements) t;

3. 获取去除扩展名的文件名 #

通过数组切片取前总长度-1个元素，再用string_agg拼接成原格式：

SELECT string_agg(elem, '.') AS file_name
FROM (
    SELECT unnest(string_to_array('file.name.pdf', '.')[:cardinality(string_to_array('file.name.pdf', '.'))-1]) AS elem
) t;
-- 返回结果：file.name

整合完整查询 #

将所有逻辑整合，可直接处理任意带扩展名的文件名：

WITH file_info AS (
    SELECT 'file.name.pdf' AS original_name,
           string_to_array('file.name.pdf', '.') AS elements,
           cardinality(string_to_array('file.name.pdf', '.')) AS elem_count
)
SELECT original_name,
       elements[elem_count] AS extension,
       string_agg(elem, '.') AS file_name
FROM file_info,
     unnest(elements[:elem_count-1]) AS elem
GROUP BY original_name, extension, elem_count;

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