0

In my Java project, I have a sequence called room_id_seq and after updating some inheritance in Spring Data JPA, now I need to drop and recreate this sequence starting from the last value + 1 from the dropped one. For example, if the last value of room_id_seq is 100, then after dropping it my newly created room_id_seq will start from 101.

So, how can I write sql to achieve this? Should I use a new name for new sequence and after creating it using the last value of previous, should I drop previous one?

  • 1
    Are you maybe looking for a solution [like this](https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync)? –  Jun 13 '22 at 12:35
  • Thanks a lot, it seems better for my situation. Voted up. –  Jun 13 '22 at 14:42

1 Answers1

0

No need to drop the sequence, just adjust the value using setval()

select setval('room_id_seq', 101);
  • Yes, but I don't now the next value. Because it will be changed based on the time when code is passed on pipeline. In this scene, I need to get the current value by querying from db. –  Jun 13 '22 at 12:31
  • @Jonathan: Then replace `101` with the query that returns you the "current value" or pass it as a parameter. –  Jun 13 '22 at 12:32