PostgreSQL: there is no unique or exclusion constraint matching the ON CONFLICT specification

Question

У меня есть две таблицы:

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 *;

Но выдает ошибку: there is no unique or exclusion constraint matching the ON CONFLICT specification

не могу понять, что делаю не так.

И по ходу вопрос... Можно ли как-то для RETURNING * вывести просто общее кол-во строк, для которых коснулось изменение? Просто RETURNING count(*) нельзя, так как нельзя использовать аггрегатную функцию здесь.

Answer 1

Решил так:

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 *;

БЛОГ НА HUSL

PostgreSQL: there is no unique or exclusion constraint matching the ON CONFLICT specification

Ответы (1 шт):