如何在PostgreSQL中创建YYYYMMDD-xxxxx格式的自增列

该需求完全可以在PostgreSQL中实现。5位长度的序号支持单自然日最多写入99999条记录，只要单日写入量不超过该上限，即可保证生成的编号全局唯一。
两种常见实现逻辑对应不同业务场景，你可以按需选择：

• 全局循环自增序号：序号跨天连续递增，到99999后自动从1重新计数，结合日期前缀不会重复，性能好、支持高并发，完全匹配你给出的示例效果
• 每日重置序号：每天的序号从00001开始重新计数，符合部分业务单号习惯，高并发场景需要额外处理锁问题避免重复

步骤1：创建循环自增序列 #

创建上限为99999的循环序列，达到最大值后自动回到1重新开始：

CREATE SEQUENCE biz_code_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 99999
    CYCLE
    START WITH 1;

说明：因为编号前缀带当日日期，只要单日插入量不超过99999，就算序列循环重置也不会出现重复编号。

步骤2：创建业务表，配置编号自动生成规则 #

创建表时给编号字段设置默认值，自动拼接当日日期和补零为5位的序号：

CREATE TABLE biz_record (
    id bigserial PRIMARY KEY,
    biz_code char(14) NOT NULL UNIQUE, -- 固定长度：8位日期 + 1位横杠 + 5位序号 = 14位
    content text,
    create_time timestamptz NOT NULL DEFAULT now()
);

-- 给biz_code字段设置默认值生成逻辑
ALTER TABLE biz_record 
ALTER COLUMN biz_code SET DEFAULT 
    to_char(current_date, 'YYYYMMDD') || '-' || lpad(nextval('biz_code_seq')::text, 5, '0');

步骤3：效果验证 #

插入测试数据后，生成的编号和你给出的示例完全一致：

20220101-00001
20220102-00002
20220102-00003
...
20220102-99999
20220701-00001
20220701-00002
...

如果业务要求每天的序号都从00001开始计数，可以通过触发器实现，无需提前创建全局序列：

步骤1：创建业务表 #

CREATE TABLE biz_record_daily (
    id bigserial PRIMARY KEY,
    biz_code char(14) NOT NULL UNIQUE,
    content text,
    create_time timestamptz NOT NULL DEFAULT now()
);

步骤2：创建编号生成的触发器函数 #

函数逻辑为插入数据时，先查询当日已存在的最大序号，在其基础上加1生成新序号：

CREATE OR REPLACE FUNCTION generate_daily_biz_code()
RETURNS TRIGGER AS $$
DECLARE
    current_seq integer;
    today_code char(8) := to_char(current_date, 'YYYYMMDD');
BEGIN
    SELECT COALESCE(MAX(SPLIT_PART(biz_code, '-', 2)::integer), 0) + 1
    INTO current_seq
    FROM biz_record_daily
    WHERE biz_code LIKE today_code || '-%';
    
    NEW.biz_code := today_code || '-' || lpad(current_seq::text, 5, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

步骤3：绑定触发器到表的插入操作 #

CREATE TRIGGER trg_gen_biz_code
BEFORE INSERT ON biz_record_daily
FOR EACH ROW
EXECUTE FUNCTION generate_daily_biz_code();

注意：该方案在每秒写入量超过10条的高并发场景下，可能出现序号重复问题，建议高并发场景下加advisory lock，或改用按日期动态创建序列的方案优化。

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