Application ids are indeed of type bigserial and autogenerated by Postgres. I wouldn’t recommend modifying this, but it may be done. For starters, foreign references to the applications table are these:
You may alter those constraints to add ON UPDATE CASCADE, which should update the references when changing the id.
Also, if you list sequences for the autoincrement columns in your DB with \ds, you’ll see that the application one is called application_id_seq. You may have it be reset with some starting number, e.g.:
ALTER SEQUENCE application_id_seq RESTART WITH 123
So after deleting an application, you may programatically shift all ids so that they cover the [1, len(applications)] interval and reset the sequence to restart from there.
Again, I wouldn’t recommend doing this: the id column is indexed and that makes writes expensive, and it’s just an internal identifier to create references, you should probably have good names for your applications to identify them.
I totally agree with you. However, what if you need a total reset of applications? Would it be nice to do a reset of the parameter “application_id_seq”? In that case, how could it be done without affecting negatively to the related tables and indexes?
Why would you need such a reset? In any case, you can do exactly what I wrote before: update the constraints, then alter the sequence. You’ll probably want to make a backup of your DB before doing this just in case.
Thanks for the answer. The only thing it comes to my mind is that maybe you want to change from a test environment to a production envrionment. So, its like you already learnt how Chirpstack works and instead of installing a new server, you just need to reset everything. What do you think?