У меня есть таблица, как показано ниже. Я пытаюсь объединиться в эту таблицу на основе значения 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, я хочу вставить / обновить записи в этой таблице. Ниже приведен код, который я использую, чтобы сделать то же самое. Когда запись уже существует в таблице, я хочу обновить таблицу на основе значения data_type_id в cte (cte_input_data.data_type_id), но это не удается с ошибкой.
Ошибка SQL [42703]: ОШИБКА: столбец исключен. Data_type_id не существует
то, что он должен достичь, это
если cte_input_data.data_type_id = 1 обновить master_config_details set date_value = cte.value
если cte_input_data.data_type_id = 2 обновить master_config_details set number_value = cte.value
если cte_input_data.data_type_id = 3 обновить master_config_details set string_value = cte.value
Приведенный ниже код должен выполнить обновление таблицы master_config_details.number_value = 22, так как в этой комбинации уже есть запись (master_config_id, account_id, row_status), которая равна (1,11,1) (запустите это, чтобы увидеть выбор записи * из master_config_details;), но вместо этого выдает ошибку
Ошибка SQL [42703]: ОШИБКА: столбец исключен. Data_type_id не существует
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';