如何合并两个带不同ON CONFLICT子句的PostgreSQL INSERT语句?
能否简化两段仅ON CONFLICT子句不同的INSERT语句?
INSERT INTO users_list ( account_id, name, email, workspace_id, user_state, outside_organisation, company_user_id, service_name ) SELECT service_data.account_id, new_user_name, sent_to, workspace_id_value, service_data.user_state, outside_organisation_value, company_user_id_value, service_data.service_name FROM unnest(email_mapping) AS service_data ON CONFLICT (service_name, email) DO UPDATE SET account_id = EXCLUDED.account_id, user_state = EXCLUDED.user_state; INSERT INTO users_list ( account_id, name, email, workspace_id, user_state, outside_organisation, company_user_id, service_name ) SELECT service_data.account_id, new_user_name, sent_to, workspace_id_value, service_data.user_state, outside_organisation_value, company_user_id_value, service_data.service_name FROM unnest(name_mapping) AS service_data ON CONFLICT (account_id, service_name) DO UPDATE SET user_state = EXCLUDED.user_state; END;
请问是否可以简化这段代码,使其更简洁美观?
补充说明
映射表结构
email_mapping和name_mapping均为数据表,结构如下:
| 列名 | 数据类型 |
|---|---|
| account_id | text |
| service_name | text |
| user_state | USER-DEFINED |
| mapping | USER-DEFINED |
其中email_mapping的mapping字段值为email,name_mapping的mapping字段值为name。
函数与目标表说明
这是一个PostgreSQL函数,数据通过函数参数传入。
users_list表结构如下:
| 列名 | 数据类型 | 是否可为空 |
|---|---|---|
| id | bigint | NO |
| created_at | timestamp with time zone | NO |
| account_id | text | NO |
| text | YES | |
| workspace_id | bigint | NO |
| user_state | USER-DEFINED | NO |
| name | text | YES |
| outside_organisation | boolean | YES |
| company_user_id | bigint | YES |
| service_name | text | NO |
| service_role | text | YES |
内容的提问来源于stack exchange,提问作者Alwaysblue
相关产品推荐
相关产品推荐

