PostgreSQL中如何按productid生成分组自增的versionnum列？

以下是几种无需复杂架构的实现方式，可在插入versionid和productid时自动生成对应分组的versionnum：

方案1：插入时直接计算（应用层处理） #

直接在插入SQL中通过子查询获取当前productid的最大versionnum，加1后赋值。无需额外数据库对象，实现最简单。

示例SQL：

INSERT INTO your_table (versionid, productid, versionnum)
VALUES ('new-uuid-value', 345, 
       COALESCE((SELECT MAX(versionnum) FROM your_table WHERE productid = 345), 0) + 1);

• 优点：轻量无额外依赖，代码直观
• 注意：高并发场景下可能出现重复编号，需配合事务或锁机制避免冲突。比如在子查询中加锁（以SQL Server为例）：

INSERT INTO your_table (versionid, productid, versionnum)
VALUES ('new-uuid-value', 345, 
       COALESCE((SELECT MAX(versionnum) FROM your_table WITH (UPDLOCK, HOLDLOCK) WHERE productid = 345), 0) + 1);

方案2：数据库触发器自动生成 #

通过数据库触发器封装编号生成逻辑，应用层只需插入versionid和productid，无需关注versionnum的计算，对应用透明。

以SQL Server为例的触发器代码：

CREATE TRIGGER trg_AssignVersionNum
ON your_table
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO your_table (versionid, productid, versionnum)
    SELECT 
        i.versionid,
        i.productid,
        COALESCE((SELECT MAX(versionnum) FROM your_table WHERE productid = i.productid), 0) + 1
    FROM inserted i;
END;

• 优点：应用层无代码侵入，逻辑统一维护在数据库，并发场景下原子性更强
• 注意：不同数据库的触发器语法略有差异，需根据使用的数据库调整（比如MySQL用BEFORE INSERT触发器直接赋值）。

补充说明 #

你提到的全局identity列id可以用于记录插入顺序，但无法直接实现分组自增。上述两种方案都无需依赖全局identity列，直接实现productid维度的独立连续编号。

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