1

I am doing Library db , sometimes I need to remove books and add a new one and I dont like how it starts missing lower ids and it just goes one . Because Serial just increade everytime . Is there a way how to do that ? For example different type of column instead of SERIAL

soulik
  • 11
  • 1
  • I'd say "live with it". Looks like a performance killer to me. – Federico klez Culloca Oct 05 '22 at 10:31
  • http://www.codediesel.com/mysql/sequence-gaps-in-mysql/ This might help (I know it's mysql, but it should mostly still apply; except for syntax) – Jurgen Rutten Oct 05 '22 at 10:49
  • You don't. Gaps in generated primary key values are expected and nothing to worry about. The **only**job for a primary key value is to be unique. It is completely meaningless if that value is 1,2,-6549, 42 or 464293. If you want to "renumber" the existing rows, you would need to UPDATE all tables that reference those values as well - something that will get ugly and complicated quickly. –  Oct 05 '22 at 11:12

1 Answers1

0

I don't know if that will solve your problem but I suggest that you do updating for all serial ids again.

So If you want it to be (1,2,3,4) so you can use this query :

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

source: How to reset sequence in postgres and fill id column with new data?

You can create a service that executes this query inside deleting books service, so after deleting books you will call this service to rearange ids

public void UpdateIds() {
// make a connection with your database and send query above to update ids
}

and in your service where you delete books, I suppose you have a service looks like that

public void deleteBooks(//some books ids to be deleted
){

//some code for deleting books by ids

// call the method above to update ids again 
UpdateIds()
    
}
Ali Zedan
  • 285
  • 3
  • 17