PostgreSQL:从选择插入的一些麻烦与冲突 [英] PostgreSQL: some troubles to insert from select with on conflict

问题描述

我有一些 Postgres 表:

I have some Postgres tables:

CREATE TABLE source_redshift.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_redshift.user_input_conf (
    collect_project_id      BIGINT NOT NULL UNIQUE,
    prompt_type             VARCHAR(20),
    prompt_input_desc       VARCHAR(300),
    prompt_input_name       VARCHAR(100),
    no_of_prompt_count      BIGINT,
    prompt_input_value      VARCHAR(100) UNIQUE,
    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_different UNIQUE (prompt_input_value,collect_project_id)

);

我需要使用以下规则将数据从 staticprompts 复制到 user_input_conf:

I need copy data from staticprompts to user_input_conf with this rules:

Primary Key : prompt_input_value_id

Unique Values : collect_project_id, prompt_input_value

Data Load Logic :

Insert only when new prompt input value is found for given collect project from source. Inputs column stores the values in JSON format in staticprompts table.

Insert :

Generate unique sequence number for each of the new prompt input value for a collect project id from source and store in prompt_input_value_id.

Update :

If prompt value already exists for a collect project and if there are any value changes on prompt_input_desc or prompt input name or prompt input value then update only those columns.

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

prompt_input_value - Inputs.value is stored in the inputs column as JSON text. Create a unique record for each inputs.value. Look at the example below this table.

我尝试使用此查询:

INSERT INTO target_redshift.user_input_conf AS t (
            collect_project_id,
            prompt_type,
            prompt_input_desc,
            prompt_input_name,
            prompt_input_value,
            script_id,
            corpuscode)
        SELECT
            s.projectid,
            s.prompttype,
            s.inputs::jsonb#>>'{inputs,0,desc}' AS desc,
            s.inputs::jsonb#>>'{inputs,0,name}' AS name,
            s.inputs::jsonb#>>'{inputs,0,values}' AS values,
            s.scriptid,
            s.corpuscode
        FROM source_redshift.staticprompts AS s
        ON CONFLICT (collect_project_id, prompt_input_value)
        DO UPDATE SET
            (prompt_input_desc, prompt_input_name, prompt_input_value, date_updated) =
            (EXCLUDED.prompt_input_desc, EXCLUDED.prompt_input_name, EXCLUDED.prompt_input_value, NOW())
        WHERE t.prompt_input_desc != EXCLUDED.prompt_input_desc
            OR t.prompt_input_name != EXCLUDED.prompt_input_name
            OR t.prompt_input_value != EXCLUDED.prompt_input_value;
    """)

但我收到一个错误:

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "user_input_conf_collect_project_id_key"
DETAIL:  Key (collect_project_id)=(1) already exists.

推荐答案

我认为有一个误解.对两列的唯一约束并不意味着每一列都是唯一的，而是两列的组合是唯一的.

I think there is a misunderstanding. A unique constraint over two columns does not mean that each of the columns is unique, but that the combination of the two columns is unique.

因此，您的 must_be_different 与 prompt_input_value 和 collect_project_id 上的唯一约束不同(并且更弱).例如，如果您有三行

So your must_be_different is different (and weaker) than the unique constraints on prompt_input_value and collect_project_id. For example, if you have the three rows

 collect_project_id | prompt_input_value
--------------------+--------------------
                  1 | a
                  1 | b
                  2 | b

它们会与单列唯一约束产生冲突，但不会与 must_be_different 产生冲突.

they will create a conflict with both single-column unique constraints, but nor with must_be_different.

我猜潜在的问题是您想使用具有多个唯一约束的 INSERT ... ON CONFLICT.那是做不到的；请参阅此问题以进行讨论和可能的解决方案.

I guess that the underlying problem is that you want to use INSERT ... ON CONFLICT with multiple unique constraints. That cannot be done; see this question for a discussion and potential solutions.

这篇关于PostgreSQL:从选择插入的一些麻烦与冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！