如何在PostgreSQL中更新表，修剪JSONB列内指定字段的空格

核心逻辑 #

PostgreSQL提供jsonb_set用于修改JSONB字段，结合trim函数可直接修剪字符串；若SITES是数组类型，则需遍历元素逐个修剪后重新聚合。

1. 单场景更新 #

场景A：SITES为字符串类型 #

UPDATE your_table
SET scope = jsonb_set(scope, '{sites}', to_jsonb(trim(scope->>'sites')))
WHERE scope ? 'sites'; -- 仅更新包含SITES字段的行

场景B：SITES为字符串数组类型 #

UPDATE your_table
SET scope = jsonb_set(
    scope,
    '{sites}',
    (SELECT jsonb_agg(trim(elem::text)) FROM jsonb_array_elements(scope->'sites') elem)
)
WHERE scope ? 'sites' AND jsonb_typeof(scope->'sites') = 'array';

2. 大规模数据迁移（分批更新） #

如果表数据量较大，直接全表更新可能导致长时间锁表，建议用分批更新脚本：

-- 第一步：备份表（操作前务必执行）
CREATE TABLE your_table_backup AS SELECT * FROM your_table;

-- 第二步：分批更新脚本
DO $$
DECLARE
    batch_size INT := 1000; -- 每批处理1000行，可根据实际调整
    updated_rows INT := batch_size;
BEGIN
    WHILE updated_rows = batch_size LOOP
        WITH target_rows AS (
            SELECT id
            FROM your_table
            WHERE scope ? 'sites'
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED -- 跳过已被锁定的行，适配并发环境
        )
        UPDATE your_table t
        SET scope = CASE
            WHEN jsonb_typeof(t.scope->'sites') = 'string' THEN
                jsonb_set(t.scope, '{sites}', to_jsonb(trim(t.scope->>'sites')))
            WHEN jsonb_typeof(t.scope->'sites') = 'array' THEN
                jsonb_set(
                    t.scope,
                    '{sites}',
                    (SELECT jsonb_agg(trim(elem::text)) FROM jsonb_array_elements(t.scope->'sites') elem)
                )
            ELSE t.scope -- 非字符串/数组类型不做修改
        END
        FROM target_rows tr
        WHERE t.id = tr.id;
        
        GET DIAGNOSTICS updated_rows = ROW_COUNT;
        COMMIT; -- 每批提交，释放锁资源
    END LOOP;
END $$;

3. 验证更新结果 #

-- 验证字符串类型SITES
SELECT 
    scope->>'sites' AS original_sites,
    trim(scope->>'sites') AS expected_trimmed,
    scope->>'sites' AS actual_trimmed
FROM your_table
WHERE scope ? 'sites' AND jsonb_typeof(scope->'sites') = 'string';

-- 验证数组类型SITES元素
SELECT 
    elem::text AS original_element,
    trim(elem::text) AS expected_trimmed,
    elem::text AS actual_trimmed
FROM your_table, jsonb_array_elements(scope->'sites') elem
WHERE scope ? 'sites' AND jsonb_typeof(scope->'sites') = 'array';

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