PostgreSQL如何截取TEXT类型列保留首尾各5行内容

固定字符偏移截取的方式完全无法适配pg_basebackup日志行长度浮动的场景，正确实现逻辑是按换行符拆分日志行，分别提取头部、尾部指定行数后重新拼接，中间插入截断标识即可。

可直接使用的实现代码 #

PostgreSQL 12+ 版本（推荐） #

以下代码默认保留前5行、最后5行日志，当日志总行数≤10行时直接返回完整内容，不会出现内容重复或截断标识误插的问题：

WITH log_lines AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY line_seq) AS row_num,
        COUNT(*) OVER (PARTITION BY id) AS total_line_count,
        line_text
    FROM hot_backup,
    -- 统一兼容Windows/Unix换行符，避免拆分出无效空行
    string_to_table(replace(log, chr(13)||chr(10), chr(10)), chr(10)) WITH ORDINALITY AS t(line_text, line_seq)
    WHERE id = 1825 -- 可按需调整查询过滤条件
)
SELECT
    string_agg(
        CASE
            WHEN row_num <= 5 THEN line_text
            WHEN row_num > total_line_count - 5 THEN line_text
            WHEN row_num = 6 THEN '========== 中间冗余日志已截断 =========='
            ELSE NULL
        END,
        chr(10)
    ) AS trimmed_backup_log
FROM log_lines
GROUP BY id;

PostgreSQL 11及更早版本兼容写法 #

老版本没有string_to_table函数，替换为regexp_split_to_table即可，逻辑完全一致：

WITH log_lines AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY line_seq) AS row_num,
        COUNT(*) OVER (PARTITION BY id) AS total_line_count,
        line_text
    FROM hot_backup,
    regexp_split_to_table(replace(log, chr(13)||chr(10), chr(10)), chr(10)) WITH ORDINALITY AS t(line_text, line_seq)
    WHERE id = 1825
)
SELECT
    string_agg(
        CASE
            WHEN row_num <= 5 THEN line_text
            WHEN row_num > total_line_count - 5 THEN line_text
            WHEN row_num = 6 THEN '========== 中间冗余日志已截断 =========='
            ELSE NULL
        END,
        chr(10)
    ) AS trimmed_backup_log
FROM log_lines
GROUP BY id;

调整说明 #

• 如果需要修改首尾保留的行数，直接修改SQL中两处判断条件的数值5即可，例如需要保留前3行、后3行就将数值改为3
• 截断提示文本可以根据自身需求自定义修改
• 换行符兼容逻辑不会对纯Unix格式的日志产生副作用，可直接保留

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