0

Setup:

I did create a table in PostgreSQL, with a column id serial Primary Key. Looking at the properties of the column in pgAdmin I can see:

Datatype: Integer

Default: nextval('items_id_seq'::regclass)

(So if I'm not mistaking i did create it using the 'serial' key-word.)

Now in my backend-code I'm using the method: jpaRepository.deleteAllInBatch(), which 'truncates' my whole table.

Problem

When after this call I do add records to the table again, i can see that the id of the following record continues from the id of the last record I had in the table.

Question:

Is there a way to reinitialize the id-serial when doing the jpaRepository.deleteAllInBatch() so that records begin with id = 0 again?

Community
  • 1
  • 1
SimonartM
  • 668
  • 2
  • 11
  • 26
  • 1
    You can reset a sequence, but why bother? You're not supposed to care about the actual values, just that they're a primary key. – Kayaman Sep 27 '19 at 12:15
  • 1
    Have a look at this question : https://stackoverflow.com/questions/49986489/reset-sequence-in-jpa?noredirect=1&lq=1 – Arnaud Sep 27 '19 at 12:15
  • I'm bothering 'cause firstly I don't use this primary key as foreign key, so resetting the sequence shouldn't create problems. And I have quite a few times where I delete 1000+ records and add 1000+ records again. I'm afraid that not this year, but maybe next year I would be reaching the sequence-integer-limit – SimonartM Sep 27 '19 at 12:19

1 Answers1

1

This may help you

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

For more detail

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

cpatel
  • 191
  • 4
  • 9