postgresql Recycle ID numbers

I have a table as follows

|GroupID | UserID |
--------------------
|1       | 1      |
|1       | 2      |
|1       | 3      |
|2       |开发者_运维技巧 1      |
|2       | 2      |
|3       | 20     |
|3       | 30     |
|5       | 200    |
|5       | 100    |

Basically what this does is create a "group" which user IDs get associated with, so when I wish to request members of a group I can call on the table.

Users have the option of leaving a group, and creating a new one.

When all users have left a group, there's no longer that groupID in my table.

Lets pretend this is for a chat application where users might close and open chats constantly, the group IDs will add up very quickly, but the number of chats will realistically not reach millions of chats with hundreds of users.

I'd like to recycle the group ID numbers, such that when I goto insert a new record, if group 4 is unused (as is the case above), it gets assigned.

There are good reasons not to do this, but it's pretty straightforward in PostgreSQL. The technique--using generate_series() to find gaps in a sequence--is useful in other contexts, too.

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;

That query will return NULL if there are no gaps or if there are no rows at all in the table "groups". If you want to use this to return the next group id number regardless of the state of the table "groups", use this instead.

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;