POSTGRES INSERT/UPDATE ON CONFLICT using WITH CTE

Question

I have a table like below. I am trying to merge into this table based on the value in a CTE. But when I try to update the table when there is a conflict, it cannot get the value in 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;`

Now using a cte I want to insert/update records in this table. Below is the code I am using to do the same. When the record already exist in the table I want to update the table based on the data_type_id value in the cte (cte_input_data.data_type_id ) but it fails with the error.

SQL Error [42703]: ERROR: column excluded.data_type_id does not exist

what it should achieve is

• if cte_input_data.data_type_id = 1 update master_config_details set date_value = cte.value

• if cte_input_data.data_type_id = 2 update master_config_details set number_value = cte.value

• if cte_input_data.data_type_id = 3 update master_config_details set string_value = cte.value

The below code should do an update to the table master_config_details.number_value = 22 as there is already a record in that combination (master_config_id, account_id, row_status) which is (1,11,1) ( run this to see the record select * from master_config_details;) but its throwing an error instead

SQL Error [42703]: ERROR: column excluded.data_type_id does not exist

   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

Special excluded table is used to reference values originally proposed for insertion. So you’re getting this error because this column doesn’t exist in your target table, and so in special excluded table. It exists only in your cte. As a workaround you can select it from cte using nested select in on conflict statement.