Search code examples
postgresqlsequencesql-insertsql-delete

Postgres - How to delete a row with its sequence?

I created a table called Product with a serial ID in Postgres:

CREATE TABLE PRODUCTS (
PRODUCT_ID serial PRIMARY KEY,
NAME VARCHAR ( 100 ) NOT NULL );

If I deleted a row with

DELETE FROM PRODUCTS WHERE PRODUCT_ID = 2

The next product to insert will have the id: 3 even if the Product_Id: 2 was already deleted, I'd like to delete the data with it's sequence so the next insert has the id: 2.

Solution

  • It's a generally bad idea. IDs are usually not the place where you need to fill the gaps.

    However, if you are only looking to do this manually, then you can insert the new row with including the ID value by hand, then sequence will not be used.

    You can reset the sequence with Postgres functions (eg. setval) , but you really shouldn't do it except in some database recovery cases maybe.

    Just get used to the fact that IDs will not be sequential. That's the norm. If you have time going around chasing sequence numbering, you might be doing something productive instead.

    If you do want sequential IDs for some non changing things like categories, just export and recreate table after you've inserted all the data, omitting ID column when importing. But remember that IDs are usually used by other tables, so you might have to update all your database every time you want to nicely renumber your IDs.