Re-use the deleted sequences of primary keys (BIGSERIAL) - No UUID to save storage-CodePudding

I am using BIGSERIAL as a Primary key and I want to make use of the deleted row's ids.

Example of the table that I want re-use the deleted ids from the deleted rows:

DELETE FROM my_table WHERE id=3;
INSERT INTO my_table(column_x) VALUES(xxxxx)

my_table

| id|column_x|        | id|column_x|               | id|column_x|
|---|--------|        |---|--------|               |---|--------|
| 1 | xxxxxx |        | 1 | xxxxxx |               | 1 | xxxxxx |
| 2 | xxxxxx |        | 2 | xxxxxx |               | 2 | xxxxxx |
| 3 | xxxxxx |   >>>  | 4 | xxxxxx |        >>>    | 4 | xxxxxx |
| 4 | xxxxxx |        | 5 | xxxxxx |               | 5 | xxxxxx |
| 5 | xxxxxx |                                     | 3 | xxxxxx |

The third table from the right inserted a new row with the id=3 in which was deleted in the table in the middle, and by doing so. I want to make use of those skipped sequences.

I made a few attempts by using ALTER SEQUENCE source

My current solution:

 INSERT INTO my_table(id, column_x)
 VALUES(deleted_index, xxxx);

Expected solution:

INSERT INTO my_table(column)
VALUES(xxxx);

In other words, altering the sequence for the index my_table.id so it re-uses the deleted sequences and move on to nextval

CodePudding user response：

dot not understand the purpose of this, but there is the code, surprisingly it works fine:

WITH selectvalues AS (
SELECT * FROM my_table 
),
deletevalue AS (
DELETE FROM my_table  del
    USING selectvalues sel
    WHERE del.id = sel.id
    RETURNING del.*
),
insertval AS(
INSERT INTO my_table 
SELECT * FROM deletevalue
RETURNING * )
SELECT * FROM insertval

CodePudding user response：

This is a fool's errand. Do you really think you will exhaust all 9223372036854775807 positive bigint numbers? Any attempt to "fill the gaps" will complicate matters and be bad for performance.