PostgreSQL更新recipients表避免sign_order字段重复值的方案咨询

1. 正确更新sign_order字段的查询 #

核心逻辑是按每个文档（consentee_id）单独生成递增的签署顺序，排序规则先遵循固定角色优先级，同角色可优先排主收件人，再按创建时间排序，保证顺序唯一：

WITH ranked_recipients AS (
  SELECT
    id,
    -- 按consentee_id分组，每个文档单独生成递增序号
    ROW_NUMBER() OVER (
      PARTITION BY consentee_id
      ORDER BY
        -- 先按固定角色优先级排序
        ARRAY_POSITION(ARRAY['CONSENTEE', 'GUARDIAN', 'ASSENTEE', 'COUNTERSIGNEE']::text[], role),
        -- 同角色优先排主收件人，可根据需求调整排序规则
        is_primary DESC,
        -- 同角色同优先级按创建时间排序，保证顺序稳定
        created_at ASC
    ) AS new_sign_order
  FROM recipients
  WHERE
    -- 过滤软删除数据，仅更新指定角色
    deleted_at IS NULL
    AND role IN ('CONSENTEE', 'GUARDIAN', 'ASSENTEE', 'COUNTERSIGNEE')
)
UPDATE recipients r
SET sign_order = rr.new_sign_order
FROM ranked_recipients rr
WHERE r.id = rr.id;

如果你的需求是全局所有收件人共用一套递增sign_order（不是按文档独立），只需要去掉窗口函数里的PARTITION BY consentee_id即可，完全符合你给出的示例输出效果。

2. 正确添加约束的SQL #

你原来的ALTER语句语法错误，PostgreSQL中多个字段修改/约束添加操作需用逗号分隔：

ALTER TABLE recipients
  ALTER COLUMN sign_order SET NOT NULL,
  ADD CONSTRAINT unique_sign_order UNIQUE (consentee_id, sign_order);

补充说明 #

• 若后续新增收件人需要自动生成sign_order，可在插入数据的逻辑中先查询对应consentee_id下的最大sign_order加1即可，或者编写触发器自动赋值
• 同角色的排序规则可根据业务需求调整，比如按邮箱、手机号排序等，只需修改OVER子句里的ORDER BY规则即可

内容的提问来源于stack exchange，提问作者Jakub