Hello i am currently try different data automation processes with python and postgreSQL. I automated the cleaning and upload of a dataset with 40.000 data emtries into my Database. Due to some flaws in my process i had to truncate some tables or data entries.

i am using: python 3.9.7 / postgeSQL 13.3 / pgAdmin 4 v.5.7

Problem

Currently i have ID's of tables who start at the ID of 44700 instead of 1 (due do my editing).

For Example a table of train stations begins with the ID 41801 and ends with ID of 83599.

Question

How can reorganize my index so that the ID starts from 1 to 41801?

After looking online i found topics like "bloat" or "reindex". I tired Vacuum or Reindex but nothing really showed a difference in my tables? As far as now my tables have no relations to each other. What would be the approach to solve my problem in postgreSQL. Some hidden Function i overlooked? Maybe it's not a problem at all, but it definitely looks weird. At some point of time i end up with the ID of 250.000 while only having 40.000 data entries in my table.

CodePudding user response：

Do you use a Sequence to generate ID column of your table? You can check it in pgAdmin under your database if you have a Sequence object in your database: Schemas -> public -> Sequences.

You can change the current sequence number with right-click on the Sequence and set it to '1'. But only do this if you deleted all rows in the table and before you start to import your data again.

As long as you do not any other table which references the ID column of your train station table, you can even update the ID with an update statement like:

UPDATE trainStations SET ID = ID - 41801 WHERE 1 = 1;