POSTGRES插入/更新冲突使用与CTE

Question

我有一张像下面这样的桌子。我试图根据CTE中的值合并到此表中。但当我试图在出现冲突时更新表时，它无法获得CTE中的值

CREATE TABLE IF NOT EXISTS master_config_details
(
    master_config_id    INT          NOT NULL,
    account_id          INT          NOT NULL,
    date_value          TIMESTAMP(3) NULL,
    number_value        BIGINT       NULL,
    string_value        VARCHAR(50)  NULL,
    row_status          SMALLINT     NOT NULL,
    created_date        TIMESTAMP(3) NOT NULL,
    modified_date       TIMESTAMP(3) NULL,
    CONSTRAINT pk_master_config_details PRIMARY KEY (master_config_id, account_id, row_status)
);

INSERT INTO master_config_details VALUES (
    1,  11, NULL,100,NULL,          0,  '2020-11-18 12:01:18',  '2020-11-18 12:02:31');

select * from master_config_details;`

现在使用cte，我想在这个表中插入/更新记录。下面是我用来做同样的事情的代码。当记录已经存在于表中时，我想根据cte（cte_input_data.data_type_id）中的data_type_id值更新表，但它失败并出错。

它应该实现的是

>

如果cte_输入_数据。数据类型id=2更新主配置详细信息集合编号cte。价值

如果cte_输入_数据。数据类型id=3更新主配置详细信息设置字符串值cte。价值

下面的代码应该更新表master_config_的详细信息。number_value=22，因为该组合中已经有一条记录（master_config_id、account_id、row_status），即（1,11,1）（运行此命令可查看从master_config_details中选择*的记录；）但它却抛出了一个错误

   WITH cte_input_data AS (
                select
                        1 AS master_config_id 
                        ,11 AS account_id
                        ,2 AS data_type_id 
                        ,'22' AS value
                        ,1 AS row_status)

    INSERT INTO master_config_details  
        SELECT 
                cte.master_config_id
                ,cte.account_id
                ,CASE WHEN cte.data_type_id  = 1 THEN cte.value::timestamp(3) ELSE NULL END AS date_time_value  
                ,CASE WHEN cte.data_type_id  = 2 THEN cte.value::integer ELSE NULL END AS number_value  
                ,CASE WHEN cte.data_type_id  = 3 THEN cte.value ELSE NULL END AS string_value 
                ,1
                ,NOW() AT TIME ZONE 'utc'
                ,NOW() AT TIME ZONE 'utc'
        FROM cte_input_data cte  
        ON CONFLICT (master_config_id,account_id,row_status)
        DO UPDATE SET
            date_value              = CASE WHEN  excluded.data_type_id = 1 THEN excluded.date_time_value::timestamp(3) ELSE NULL END 
            ,number_value               = CASE WHEN  excluded.data_type_id = 2 THEN excluded.number_value::integer ELSE NULL END 
     
            ,string_value           = CASE WHEN  excluded.data_type_id = 3 THEN excluded.string_value ELSE NULL END 
            ,modified_date = NOW() AT TIME ZONE 'utc';



   

Answer 1

特殊排除表用于引用最初建议插入的值。所以您会得到这个错误，因为该列不存在于您的目标表中，也不存在于特殊排除表中。它只存在于您的cte中。作为一种解决方法，您可以使用嵌套的选择冲突语句从cte中选择它。