PostgreSQL：运算符不存在：jsonb <> 字符不同

将数据从一种模式传输到另一种模式时，必须满足以下条件：

prompt_input_value_id
Generate a unique sequence number for the combination of each prompt_input_value and collect_project_id

我有以下查询工作正常并在 PostgreSQL 中完成工作：

INSERT INTO target.table AS t (
        collect_project_id,
        prompt_type,
        prompt_input_desc,
        prompt_input_name,
        prompt_input_value,
        script_id,
        corpuscode)
    SELECT s.projectid,
           max(s.prompttype),
           max(el.inputs->>'name') AS name,
           max(el.inputs->>'desc') AS description,
           v.value,
           max(s.scriptid),
           max(s.corpuscode)
    FROM source.table AS s
       CROSS JOIN LATERAL jsonb_array_elements(s.inputs::jsonb) AS el(inputs)
       CROSS JOIN LATERAL jsonb_array_elements(el.inputs->'values') AS v(value)
    WHERE
        s.prompttype = 'input' AND (s.created > now() - interval '30 minutes' OR s.modified > now() - interval '30 minutes')
    GROUP BY s.projectid, v.value
    ON CONFLICT
        (prompt_input_value, collect_project_id)
    DO UPDATE SET
        (prompt_input_desc, prompt_input_name, date_updated) =
        (EXCLUDED.prompt_input_desc,
        EXCLUDED.prompt_input_name,
        NOW())
    WHERE t.prompt_input_desc != EXCLUDED.prompt_input_desc
        OR t.prompt_input_name != EXCLUDED.prompt_input_name;

我需要以接受 RedShift 的方式重新制作它。例如，UPSERT (ON CONFLICT) RedShift 不接受。

因此，我想将基本查询分为两部分 - 添加数据和更新。

我通过 WITH 语句添加：

    WITH all_values AS (
    SELECT s.projectid AS projectid,
           max(s.prompttype) AS prompttype,
           max(el.inputs->>'name') AS name,
           max(el.inputs->>'desc') AS description,
           v.value AS value,
           max(s.scriptid) AS scriptid,
           max(s.corpuscode) AS corpuscode
    FROM source.table AS s
       CROSS JOIN LATERAL jsonb_array_elements(s.inputs::jsonb) AS el(inputs)
       CROSS JOIN LATERAL jsonb_array_elements(el.inputs->'values') AS v(value)
    WHERE
        s.prompttype = 'input' AND (s.created > now() - interval '30 minutes' OR s.modified > now() - interval '30 minutes')
    GROUP BY s.projectid, v.value
   ), unique_items AS (
    SELECT all_values.projectid, all_values.prompttype, all_values.name, all_values.description, all_values.value, all_values.scriptid, all_values.corpuscode
    FROM all_values
    WHERE all_values.projectid != (SELECT collect_project_id FROM target.table) AND all_values.value != (SELECT prompt_input_value FROM target.table)
   )
INSERT INTO target.table (
        collect_project_id,
        prompt_type,
        prompt_input_desc,
        prompt_input_name,
        prompt_input_value,
        script_id,
        corpuscode)
        SELECT s.projectid,
               s.prompttype,
               s.name,
               s.description,
               s.value,
               s.scriptid,
               s.corpuscode
        FROM all_values AS s;

我的逻辑是这样的：

• 首先，我获取所有需要添加的条目（我为字典的每个元素从它们中形成单独的行）
• 我只选择那些在我要添加的表中没有找到两列 prompt_input_value 和 collect_project_id 的值组合的行
• 我将结果写入目标表。

但我收到错误错误：运算符不存在：jsonb <> 字符变化提示：没有运算符与给定名称和参数类型匹配。您可能需要添加显式类型转换。职位：1006

你能澄清我哪里出错了吗？

提前致谢！