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:
TABLE "device" CONSTRAINT "device_application_id_fkey" FOREIGN KEY (application_id) REFERENCES application(id)
TABLE "integration" CONSTRAINT "integration_application_id_fkey" FOREIGN KEY (application_id) REFERENCES application(id) ON DELETE CASCADE
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.