主页 / 问题 / 1321546
Accepted
Viktor Andriichuk
Asked:2022-08-26 18:33:01 +0000 UTC2022-08-26 18:33:01 +0000 UTC

PostgreSQL：没有与 ON CONFLICT 规范匹配的唯一或排除约束

  • 772

我有两张桌子：

CREATE TABLE source.staticprompts (
    id              INT,
    projectid       BIGINT,
    scriptid        INT,
    promptnum       INT,
    prompttype      VARCHAR(20),
    inputs          VARCHAR(2000),
    attributes      VARCHAR(2000),
    text            VARCHAR(2000),
    corpuscode      VARCHAR(2000),
    comment         VARCHAR(2000),
    created         TIMESTAMP,
    modified        TIMESTAMP


);


CREATE TABLE target.dim_collect_user_inp_configs (
    collect_project_id      BIGINT NOT NULL,
    prompt_type             VARCHAR(20),
    prompt_input_desc       VARCHAR(3000),
    prompt_input_name       VARCHAR(1000),
    no_of_prompt_count      BIGINT,
    prompt_input_value      VARCHAR(100),
    prompt_input_value_id   BIGSERIAL PRIMARY KEY,
    script_id               BIGINT,
    corpuscode              VARCHAR(20),
    min_recordings          VARCHAR(2000),
    max_recordings          VARCHAR(2000),
    recordings_count        VARCHAR(2000),
    lease_duration          VARCHAR(2000),
    date_created            TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
    date_updated            TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT must_be_unique UNIQUE (prompt_input_value, collect_project_id)

);

我需要将数据从 source.staticprompts 复制到 target.dim_collect_user_inp_configs，如果发现 prompt_input_value 已经有一个值，那么我需要查看 prompt_input_name 和 prompt_input_desc 是否已更改，如果是，请更新它（连同更新日期）。

我正在尝试进行这样的查询：

INSERT INTO target.dim_collect_user_inp_configs AS t (
            collect_project_id,
            prompt_type,
            prompt_input_desc,
            prompt_input_name,
            prompt_input_value,
            script_id,
            corpuscode)
        SELECT
            s.projectid,
            s.prompttype,
            el.inputs->>'name' AS name,
            el.inputs->>'desc' AS description,
            jsonb_array_elements(el.inputs->'values') AS value,
            s.scriptid,
            s.corpuscode
        FROM  source.staticprompts AS s,
            jsonb_array_elements(s.inputs::jsonb) el(inputs)
        ON CONFLICT
            (prompt_input_value)
        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
RETURNING *;

但它给出了一个错误：没有与 ON CONFLICT 规范匹配的唯一或排除约束

我无法弄清楚我做错了什么。

在此过程中，问题... RETURNING * 是否有可能仅显示更改影响的总行数？不允许仅返回 count(*)，因为您不能在此处使用聚合函数。

postgresql

1 个回答

  1. Best Answer
    Решил так:

INSERT INTO target.dim_collect_user_inp_configs 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.staticprompts 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)
        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
RETURNING *;
    • 0

相关问题