PostgreSQL触发器逐行将text列转为JSONB[]的问题排查

开发者阿泽

2026-7-12

问题分析与解决

原触发器代码的问题

未明确引用新行数据：触发器函数中直接写data而非NEW.data，写法存在歧义，易引发逻辑错误。
无效的类型转换：PostgreSQL不支持直接将text或text[]强制转换为jsonb[]，必须逐个将数组元素解析为jsonb后再重组数组。
触发器无法绕过类型检查：这是核心问题——当Azure Data Factory传入text[]类型的值，而目标表列是jsonb[]时，PostgreSQL会在解析INSERT语句的阶段直接抛出类型不匹配错误，触发器根本没有执行的机会。

解决方案

方案1：创建自定义隐式类型转换

让PostgreSQL自动将text[]转换为jsonb[]，绕过类型检查错误，无需修改触发器或ADF配置：

-- 创建转换函数：将text[]的每个元素转为jsonb，再重组为jsonb[]
CREATE OR REPLACE FUNCTION text_array_to_jsonb_array(text[])
RETURNS jsonb[] AS $$
SELECT array_agg(x::jsonb) FROM unnest($1) x;
$$ LANGUAGE sql IMMUTABLE;

-- 创建隐式转换规则，让PostgreSQL自动使用上述函数转换类型
CREATE CAST (text[] AS jsonb[]) WITH FUNCTION text_array_to_jsonb_array(text[]) AS IMPLICIT;

方案2：修正触发器函数（若需保留触发器逻辑）

若必须通过触发器处理转换，需确保触发器能被执行——可先将目标表的data列临时改为text[]，或使用中间列，再通过触发器转换为jsonb[]。修正后的触发器函数如下：

CREATE OR REPLACE FUNCTION cast_to_jsonb_function()
RETURNS TRIGGER 
LANGUAGE PLPGSQL
AS
$$
BEGIN
    -- 针对text[]类型的输入，逐个元素转为jsonb后重组数组
    SELECT array_agg(x::jsonb) INTO NEW.data FROM unnest(NEW.data::text[]) x;
    RETURN NEW;
END;
$$;

-- 重新创建触发器（确保表列类型匹配或已配置类型转换）
CREATE TRIGGER trigger_cast_for_each_row
BEFORE INSERT ON public."ObjectSource"
FOR EACH ROW EXECUTE PROCEDURE cast_to_jsonb_function();