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
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