PostgreSQL 14如何将带[]的字符串列表拆分展开为多个新列

你当前SQL返回多行重复数据的核心原因是误用了unnest()函数：该函数的逻辑是将数组拆分为多行返回，和你需要的「拆分元素后按顺序生成独立列」的需求方向不符。

不需要嵌套两层replace()，两种更简洁的写法：

• 首尾字符裁剪：直接用trim(classes, '[]')，可以一次性移除字符串首尾所有的[和]字符，写法最简洁。
• 直接转原生数组：如果你的classes字段是标准的数组格式字符串，直接写classes::text[]即可将其转换为PostgreSQL原生数组类型，不需要手动处理括号。

以下SQL可以直接输出你需要的列格式，不会产生重复行：

写法1：直接转换数组类型（推荐） #

PostgreSQL数组下标从1开始，直接按下标提取对应位置的元素作为独立列即可：

SELECT
  text,
  classes::text[] [1] AS class_1,
  classes::text[] [2] AS class_2,
  classes::text[] [3] AS class_3
FROM tbl;

如果你的数组元素在逗号后存在空格（比如示例中的[food, drink]），转数组后对应元素会带前置空格，可使用下面的写法提前处理分隔符。

写法2：字符串拆分后提取（适配带空格的场景） #

拆分时直接用逗号+空格作为分隔符，自动处理元素前后的空格：

SELECT
  text,
  arr[1] AS class_1,
  arr[2] AS class_2,
  arr[3] AS class_3
FROM (
  SELECT
    text,
    string_to_array(trim(classes, '[]'), ', ') AS arr
  FROM tbl
) t;

执行后返回的结果和预期格式完全一致：

text                class_1   class_2  class_3
some string         food       drink      
another string      food     medicine   drink
another random      car

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