PostgreSQL中如何为event表生成E0001至E9999格式自定义编码

1. 创建编码专用自增序列 #

用于生成四位数字后缀，取值范围1-9999：

CREATE SEQUENCE event_code_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
CYCLE; -- 若不需要超过9999后从头循环，可改为NO CYCLE，超过后插入会报错

2. 添加字段校验规则 #

确保所有写入的code都符合E+四位数字的格式要求，非法格式会直接拦截：

ALTER TABLE event
ADD CONSTRAINT check_event_code_format 
CHECK (code ~ '^E\d{4}$');

如果需要避免重复编码，可额外添加唯一约束：

ALTER TABLE event
ADD CONSTRAINT unique_event_code UNIQUE (code);

3. 创建插入逻辑触发器函数 #

实现「未指定code则自动生成，已指定则直接使用」的逻辑：

CREATE OR REPLACE FUNCTION set_event_code()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.code IS NULL THEN
    -- 用LPAD左补0将序列值格式化为4位字符串，拼接前缀E
    NEW.code := 'E' || LPAD(nextval('event_code_seq')::TEXT, 4, '0');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

4. 绑定触发器到event表 #

触发器在插入操作执行前触发，逐行处理code字段：

CREATE TRIGGER trigger_event_before_insert
BEFORE INSERT ON event
FOR EACH ROW
EXECUTE FUNCTION set_event_code();

效果验证 #

• 未指定code插入：INSERT INTO event (name, created_by) VALUES ('测试活动', 'admin');，系统自动生成符合规则的code，首次插入为E0001
• 指定code插入：INSERT INTO event (code, name, created_by) VALUES ('E1234', '自定义活动', 'admin');，直接使用传入的code，格式不符合/重复（加了唯一约束的话）会被拦截报错

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