CODEWITHSUNDEEP
postgresql
Miguel Peniche
Miguel Peniche

Reputation: 1032

Keeping id continuity after deletion of rows

How can you keep the continuity of id after deleting a row?

For example: You have 10 rows in a table and then you delete the last 5, so the next creation will be id 11, but 6, 7, 8, 9 and 10 are gone!. So I want the new creation to be just 6, for continuity.

Upvotes: 1

Views: 688

Answers (1)

clemens
clemens

Reputation: 17721

You can do that with a self join:

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL

This query searches the first t1.id in the table tab whose successor is not in tab.

Example:

SELECT id FROM tab ORDER BY id;
 id
____

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;
 id 
____
  1

INSERT INTO tab VALUES (1), (2), (3), (4), (6), (7);

SELECT id FROM tab ORDER BY id;
 id
____
  1
  2
  3
  4
  6
  7

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;
 id 
____
  5

INSERT INTO tab VALUES (5)

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;
 id 
____
  8

But note that this method of obtaining primary keys can be problematic for parallel calls. If you have multiple parallel threads generating primary keys in this way, conflicts can easily occur. The id column should always have the primary key constraint, and you should protect access with appropriate locks or serialized transactions if necessary.

Upvotes: 1

Related Questions