CODEWITHSUNDEEP
postgresql
Viktor Andriichuk
Viktor Andriichuk

Reputation: 123

PostgreSQL: ON CONFLICT DO UPDATE command cannot affect row a second time

I have two PostreSQL tables:

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


);

and

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)

);

I need copy data from source to target with this conditions:

Each value need to be stored as one row in the dim_collect_user_inp_configs table. Example, Indoor-Loud as one row and it will have it’s own unique identifier as prompt_input_value_id, Indoor-Normal as one row and it will have it’s own unique identifier as prompt_input_value_id till the Semi-Outdoor-Whisper.

There could be multiple input “name” in one inputs column. Each name and its value need to be stored separately.

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

Source table have this data:

20030,input,m66,,null,"[{""desc"": ""Select the setting that you will do the recordings under."", ""name"": ""ambient"", ""type"": ""dropdown"", ""values"": ["""", ""Indoors + High + Loud"", ""Indoors + High + Normal"", ""Indoors + Low + Normal"", ""Indoors + Low + LowVolume"", ""Outdoors + High + Normal"", ""Outdoors + Low + Loud"", ""Outdoors + Low + Normal"", ""Outdoors + Low + LowVolume""]}, {""desc"": ""Select the noise type that you will do the recordings under."", ""name"": ""Noise type"", ""type"": ""dropdown"", ""values"": ["""", ""Human Speech"", ""Ambient Speech"", ""Non-Speech""]}]",,2018-12-13 13:49:24.408933,1,5,5906,2021-08-26 12:43:54.061000

I try to do this task with this query:

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

But I get an error:

ON CONFLICT DO UPDATE command cannot affect row a second time Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Can you help where is mistake?

Upvotes: 1

Views: 2587

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247043

Change the SELECT so that all rows with the same prompt_input_value and collect_project_id are grouped together, then each targer row will be updated at most once. Use aggregate functions for all other columns.

Something like

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

Upvotes: 2

Related Questions