PostgreSQL插入数据时调用函数自动生成类YouTube式ID的方法

前提说明 #

假设你已经有一个整数转复杂ID的函数int_to_complex_id(bigint)，该函数能将自增整数转换为唯一的字符串ID（类似YouTube的格式）。以下是实现自动赋值的具体步骤：

1. 调整表结构（从bigserial转为字符串类型） #

首先需要把原来的bigserial类型的id列改为字符串类型，并将现有数据的ID转换为复杂格式：

-- 先将现有整数ID转换为复杂ID
UPDATE persons SET id = int_to_complex_id(id::bigint);

-- 修改id列类型为text（或根据需求用varchar(n)指定长度）
ALTER TABLE persons ALTER COLUMN id TYPE text;

-- 移除bigserial自带的默认序列值
ALTER TABLE persons ALTER COLUMN id DROP DEFAULT;

2. 实现自动生成逻辑（两种可选方案） #

方案一：使用列默认值（简单直接）

利用原bigserial对应的序列（通常名为persons_id_seq），将序列的自增值传入转换函数作为id的默认值：

-- 设置id列默认值为转换后的复杂ID
ALTER TABLE persons ALTER COLUMN id SET DEFAULT int_to_complex_id(nextval('persons_id_seq'::regclass));

之后插入数据时，无需指定id字段，数据库会自动生成并赋值。

方案二：使用触发器（适合需额外逻辑的场景）

如果需要在生成ID前添加额外判断（比如禁止手动指定重复ID），可以用触发器实现：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION generate_complex_id()
RETURNS TRIGGER AS $$
BEGIN
  -- 仅当未手动指定id时自动生成
  IF NEW.id IS NULL THEN
    NEW.id = int_to_complex_id(nextval('persons_id_seq'::regclass));
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到persons表的插入操作
CREATE TRIGGER trigger_persons_generate_id
BEFORE INSERT ON persons
FOR EACH ROW
EXECUTE FUNCTION generate_complex_id();

关键注意事项 #

• 唯一性保障：确保int_to_complex_id是一一映射的函数（即不同整数对应不同字符串ID），结合序列的自增特性，就能保证ID全局唯一。
• 序列起始值：如果原序列丢失，可重新创建并设置起始值为现有最大整数ID+1：

  CREATE SEQUENCE persons_id_seq START WITH (SELECT MAX((regexp_replace(id, '[^0-9]', '', 'g'))::bigint) FROM persons) + 1;
  

  （注：这里的正则替换需要根据你的复杂ID格式调整，目的是提取原整数ID；如果无法提取，可根据实际数据设置起始值）
• 约束维护：确保id列保留唯一约束或主键约束，避免重复：

  -- 若原主键已丢失，重新添加主键约束
  ALTER TABLE persons ADD PRIMARY KEY (id);
  

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