PostgreSQL：如何为user表substitute_user_id列设默认值为自身ID

需求概述 #

在PostgreSQL的public.user表中，substitute_user_id列用于指定代用户负责的人员ID；若用户自行负责，该列需指向自身ID，确保总能通过该字段获取负责用户的正确ID。需实现：

1. 插入数据时自动将substitute_user_id设为当前行的id；
2. 允许后续通过UPDATE语句将该列修改为其他user.id。

当前表结构与索引 #

create table public.user (
  id bigserial primary key,
  is_superuser boolean not null,
  substitute_user_id bigint,
  foreign key (substitute_user_id) references public.user (id)
  match simple on update no action on delete no action
);
create index user_substitute_user_id_d012d5b2 on user using btree (substitute_user_id);

实现方案 #

由于PostgreSQL的自增ID（bigserial）在插入时才生成，无法直接用DEFAULT值引用当前行的id，因此需要通过触发器实现自动赋值逻辑。

1. 创建触发器函数 #

该函数会在插入行前检查substitute_user_id，若未指定则自动设为当前行的id：

CREATE OR REPLACE FUNCTION set_substitute_user_id_default()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.substitute_user_id IS NULL THEN
    NEW.substitute_user_id := NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建BEFORE INSERT触发器 #

将触发器函数绑定到public.user表的插入操作：

CREATE TRIGGER trigger_user_set_substitute_default
BEFORE INSERT ON public.user
FOR EACH ROW
EXECUTE FUNCTION set_substitute_user_id_default();

3. 优化（可选） #

为确保substitute_user_id始终有值，可将其设为非空约束：

ALTER TABLE public.user ALTER COLUMN substitute_user_id SET NOT NULL;

4. 补全现有数据（若需） #

如果表中已有未设置substitute_user_id的行，执行以下语句补全：

UPDATE "user" SET "substitute_user_id" = "user"."id" WHERE substitute_user_id IS NULL;

效果说明 #

• 插入数据时，若未显式指定substitute_user_id，会自动赋值为当前行的id；若插入时指定了其他用户ID，则保留指定值。
• 后续可通过常规UPDATE语句自由修改substitute_user_id的值，触发器仅作用于插入操作，不影响更新逻辑。

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