PostgreSQL character varying[]数组未返回首部NULL元素如何获取完整数组

问题原因 #

你遇到的现象是PostgreSQL原生支持非1起始的自定义数组下标，你当前存储的details字符数组的下标范围为2~10，因此直接查询时不会包含下标1的位置，要得到从1开始、带前置NULL的完整数组，可以使用以下两种方案：

方案1：数组拼接（性能更高，适合连续下标的数组） #

通过array_lower获取数组的起始下标，生成对应长度的NULL数组和原数组拼接即可：

SELECT 
  array_fill(NULL::character varying, ARRAY[array_lower(details, 1) - 1]) || details AS full_details
FROM x 
WHERE id = 1;

执行后得到的结果就是你需要的{NULL,wfeweqf,NULL,NULL,wefwf,NULL,NULL,NULL,NULL,wqfewef}。

方案2：序列遍历聚合（兼容性更强，支持下标不连续的数组） #

生成从1到数组最大下标的连续序列，逐个取对应位置的元素后重新聚合为数组，哪怕数组中间有下标断层也会自动补NULL：

SELECT 
  array_agg(details[i] ORDER BY i) AS full_details
FROM x,
     generate_series(1, array_upper(details, 1)) AS t(i)
WHERE id = 1
GROUP BY id;

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