You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何合并两个带不同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_idtext
service_nametext
user_stateUSER-DEFINED
mappingUSER-DEFINED

其中email_mapping的mapping字段值为email,name_mapping的mapping字段值为name。

函数与目标表说明

这是一个PostgreSQL函数,数据通过函数参数传入。

users_list表结构如下:

列名数据类型是否可为空
idbigintNO
created_attimestamp with time zoneNO
account_idtextNO
emailtextYES
workspace_idbigintNO
user_stateUSER-DEFINEDNO
nametextYES
outside_organisationbooleanYES
company_user_idbigintYES
service_nametextNO
service_roletextYES

内容的提问来源于stack exchange,提问作者Alwaysblue

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.07.08 17:33:20