sqlpostgresqlsequenceprimary-keyalter

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


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


Solution

  • 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