PostgreSQL如何按列字符串长度拆分单行数据为多行？

需求说明 #

现有如下PostgreSQL表数据：

+---------------+---------------+-------------+
| employee_name |     role      | date_joined |
+---------------+---------------+-------------+
| John          |      10013004 | 2018-01-09  |
| Jane          |          1004 | 2020-08-09  |
| Sam           |  100380003000 | 2022-03-31  |
+---------------+---------------+-------------+

需要将role列的字符串按每4个字符为一组拆分，每组作为新行的role值，其他列信息保持对应，最终得到如下结果：

+---------------+-------+-------------+
| employee_name | role  | date_joined |
+---------------+-------+-------------+
| John          |  1001 | 2018-01-09  |
| John          |  3004 | 2018-01-09  |
| Jane          |  1004 | 2020-08-09  |
| Sam           |  1003 | 2022-03-31  |
| Sam           |  8000 | 2022-03-31  |
| Sam           |  3000 | 2022-03-31  |
+---------------+-------+-------------+

实现方法 #

可以利用PostgreSQL的generate_series函数生成拆分位置序列，结合substring函数截取指定长度的字符串，同时用trim处理role列的多余空格，具体SQL语句如下：

SELECT
    employee_name,
    trim(substring(trim(role) FROM (n * 4 - 3) FOR 4)) AS role,
    date_joined
FROM
    your_table_name,
    generate_series(1, ceil(length(trim(role)) / 4)::int) AS n
ORDER BY
    employee_name, n;

代码解释 #

• trim(role)：先去除role字段前后的空格，确保计算长度和截取的准确性
• length(trim(role)) / 4：计算需要拆分的组数，用ceil向上取整（比如长度为12的字符串会得到3组）
• generate_series(1, ...)：生成从1到拆分组数的序列，每个数值对应一组的起始位置
• substring(trim(role) FROM (n * 4 - 3) FOR 4)：根据序列值n计算每组的起始位置，截取4个字符的片段，再用trim处理可能的末尾空格
• 最后按employee_name和序列n排序，保证结果顺序和示例一致

注意事项 #

如果role列存在长度不是4的倍数的情况，最后一组会截取剩余的字符；如果需要补全到4位，可以结合lpad或rpad函数处理。

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