Postgres数组转多行：反向array_agg()方法及位置列需求

嘿，你说的反向array_agg()其实就是PostgreSQL里的unnest()函数，正好能满足你的需求！而且要保留元素在原数组中的位置，还可以用with ordinality来实现，再结合窗口函数生成自增的oid，完全能得到你想要的结果。

我给你一步步拆解实现方法：

核心思路 #

1. 先把NUMBERS字段的逗号分隔字符串转换成数组：用string_to_array()函数，注意你的分隔符是, （逗号加空格），要和原数据匹配。
2. 把数组拆分成多行，同时获取元素的位置：用unnest(...) with ordinality，这里的ordinality就是元素在原数组中的位置序号。
3. 生成自增的oid：用row_number()窗口函数，按街道和位置排序来生成连续的序号。

完整SQL代码 #

假设你的原表名为street_numbers，那么可以用下面的查询得到目标结果：

SELECT
  row_number() OVER (ORDER BY street, ordinality) AS oid,
  street,
  split_numbers AS numbers,
  ordinality AS position
FROM (
  SELECT
    street,
    unnest(string_to_array(numbers, ', ')) AS split_numbers,
    ordinality
  FROM street_numbers
) AS split_data;

代码解释 #

• string_to_array(numbers, ', ')：把NUMBERS字段的字符串（比如'01, 03'）转换成PostgreSQL数组['01','03']。
• unnest(...) with ordinality：将数组拆分成单独的行，同时给每一行添加一个ordinality列，记录该元素在原数组中的位置（从1开始计数）。
• row_number() OVER (ORDER BY street, ordinality)：按街道名称和元素位置排序，生成连续的自增序号作为oid。

测试结果 #

用你提供的原表数据测试，会得到完全符合你要求的输出：

如果需要把结果保存成新表，可以在查询开头加上CREATE TABLE new_table AS，这样就能直接生成目标结构的新表啦！

内容的提问来源于stack exchange，提问作者A.T.