如何在PostgreSQL中实现自定义字母数字序列的JobID？

当然可以搞定！PostgreSQL有好几种优雅的方式自动生成这种带前缀的序列ID，完全不用你手动拼接。下面我分享两个实用方案，你可以根据自己的业务场景选：

方案1：序列+触发器（适合固定前缀的场景） #

如果每个公司的前缀（比如APL、MSF）是固定且提前确定的，给每个公司单独建序列，再用触发器自动拼接ID就很合适。

步骤1：创建任务表 #

先建个存任务的主表，确保公司前缀是3位大写字母，同时预留JobID字段：

CREATE TABLE jobs (
    job_id VARCHAR(10) PRIMARY KEY,
    company_code CHAR(3) NOT NULL CHECK (company_code ~ '^[A-Z]{3}$'), -- 强制3位大写字母前缀
    task_details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

步骤2：为每个公司创建专属序列 #

比如给苹果（APL）和微软（MSF）各建一个序列，从1开始计数：

-- 苹果的任务编号序列
CREATE SEQUENCE seq_apl_jobs START 1;
-- 微软的任务编号序列
CREATE SEQUENCE seq_msf_jobs START 1;

步骤3：写个触发器函数 #

这个函数会根据插入的公司前缀，自动调用对应的序列生成JobID：

CREATE OR REPLACE FUNCTION generate_job_id()
RETURNS TRIGGER AS $$
BEGIN
    CASE NEW.company_code
        WHEN 'APL' THEN
            NEW.job_id := 'APL-' || nextval('seq_apl_jobs');
        WHEN 'MSF' THEN
            NEW.job_id := 'MSF-' || nextval('seq_msf_jobs');
        -- 以后加新公司，直接在这里加分支就行
        ELSE
            RAISE EXCEPTION '不支持的公司编码: %', NEW.company_code;
    END CASE;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤4：绑定触发器到任务表 #

让触发器在插入新任务时自动执行上面的函数：

CREATE TRIGGER trigger_generate_job_id
BEFORE INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION generate_job_id();

测试一下效果 #

插几条测试数据试试：

INSERT INTO jobs (company_code, task_details) VALUES ('APL', '苹果的第一个任务');
INSERT INTO jobs (company_code, task_details) VALUES ('MSF', '微软的第一个任务');
INSERT INTO jobs (company_code, task_details) VALUES ('APL', '苹果的第二个任务');

查询jobs表会得到：

job_id | company_code | task_details       | created_at
-------+--------------+--------------------+-------------------
APL-1  | APL          | 苹果的第一个任务   | 2024-05-20 10:00:00
MSF-1  | MSF          | 微软的第一个任务   | 2024-05-20 10:00:01
APL-2  | APL          | 苹果的第二个任务   | 2024-05-20 10:00:02

方案2：全局编号表+触发器（适合动态加公司的场景） #

如果公司数量不确定，经常要新增，方案1维护起来就麻烦了。这时候可以用一个单独的表记录每个公司的当前任务编号，触发器自动更新并生成ID。

步骤1：创建公司编号记录表 #

这个表专门存每个公司的当前最大任务数：

CREATE TABLE company_job_counters (
    company_code CHAR(3) PRIMARY KEY CHECK (company_code ~ '^[A-Z]{3}$'),
    current_count INT DEFAULT 0
);

先把已有的公司加进去：

INSERT INTO company_job_counters (company_code) VALUES ('APL'), ('MSF');

步骤2：创建任务表 #

和方案1类似，但不需要单独的序列：

CREATE TABLE jobs (
    job_id VARCHAR(10) PRIMARY KEY,
    company_code CHAR(3) NOT NULL REFERENCES company_job_counters(company_code),
    task_details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

步骤3：写触发器函数 #

这个函数会原子性地更新公司的计数，然后生成对应的JobID（避免并发插入时重复编号）：

CREATE OR REPLACE FUNCTION generate_dynamic_job_id()
RETURNS TRIGGER AS $$
DECLARE
    new_count INT;
BEGIN
    -- 用UPDATE ... RETURNING保证原子性，高并发下也不会出重复编号
    UPDATE company_job_counters
    SET current_count = current_count + 1
    WHERE company_code = NEW.company_code
    RETURNING current_count INTO new_count;

    NEW.job_id := NEW.company_code || '-' || new_count;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤4：绑定触发器 #

CREATE TRIGGER trigger_generate_dynamic_job_id
BEFORE INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION generate_dynamic_job_id();

测试动态场景 #

新增一个谷歌（GOOG）公司，然后插任务：

INSERT INTO company_job_counters (company_code) VALUES ('GOOG');
INSERT INTO jobs (company_code, task_details) VALUES ('GOOG', '谷歌的第一个任务');

查询jobs表会得到GOOG-1，同时company_job_counters里GOOG的current_count会变成1。

一些注意点 #

• 并发安全：两个方案都考虑了并发问题——方案1的序列是PostgreSQL原生原子性的，方案2用了UPDATE ... RETURNING，都不会出现重复编号。
• 性能：如果任务量超大，方案1的序列会比方案2快一点；但方案2更灵活，大多数业务场景下性能完全够用。
• 格式校验：通过CHECK约束确保公司前缀是3位大写字母，避免生成不符合要求的ID。

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