postgresql 回收 ID 号

Question

我有一个表格如下

|GroupID | UserID |
--------------------
|1       | 1      |
|1       | 2      |
|1       | 3      |
|2       | 1      |
|2       | 2      |
|3       | 20     |
|3       | 30     |
|5       | 200    |
|5       | 100    |

基本上，它所做的是创建一个与用户 ID 相关联的“组”，因此当我希望请求组成员时，我可以在表上调用。

用户可以选择离开群组并创建新群组。

当所有用户都离开一个组时，我的表中不再有该组 ID。

假设这是一个聊天应用程序，用户可能会不断地关闭和打开聊天，组 ID 会很快加起来，但实际上聊天的数量不会达到数百个用户的数百万次聊天。

我想回收组 ID 号，这样当我插入新记录时，如果组 4 未使用(如上例)，它就会被分配。

Answer 1

有充分的理由不这样做，但在 PostgreSQL 中它非常简单。该技术——使用 generate_series() 查找序列中的空缺——在其他情况下也很有用。

WITH group_id_range AS (
    SELECT generate_series((SELECT MIN(group_id) FROM groups), 
                           (SELECT MAX(group_id) FROM groups)) group_id
)
SELECT min(gir.group_id)
FROM group_id_range gir
LEFT JOIN groups g ON (gir.group_id = g.group_id)
WHERE g.group_id IS NULL;

如果“组”表中没有间隙或根本没有行，则该查询将返回 NULL。如果您想使用它来返回下一个组 ID 号，而不管表“groups”的状态如何，请改用它。

WITH group_id_range AS (
    SELECT generate_series(
                      (COALESCE((SELECT MIN(group_id) FROM groups), 1)), 
                      (COALESCE((SELECT MAX(group_id) FROM groups), 1))
                  ) group_id
)
SELECT COALESCE(min(gir.group_id), (SELECT MAX(group_id)+1 FROM groups))
FROM group_id_range gir
LEFT JOIN groups g ON (gir.group_id = g.group_id)
WHERE g.group_id IS NULL;