Question 90173 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search