postgresqlprimary-keyimport-csvdatabase-sequence

SERIAL PRIMARY KEY keeps counting when the csv importing file fails in pgAdmin

I have table with SERIAL PRIMARY KEY and when importing csv file with some data and it fails for any reason, when importing successes the PRIMARY KEY instead of starting from zero (in case the table was empty) it starts from the number that it was suppoused to be if the importing action hadn't failed like the screen shot shown below.

Data output

I have found some solutions but in my case doesn't work like TRUNCATE TABLE. I applied it and TRUNCATE TABLE and all data get deleted but when importing again the SERIAL number keeps counting in a sequencial way.

I would like to know a way of avoiding this because in the future when I have table with more data it would be a huge problem to fix so is there any way of fixing it WITHOUT deleting or dropping table ?

Thank you very much

Solution

  • you can truncate table with reset identity option :

    truncate table xxx RESTART IDENTITY ;

    but for identity column I recommend modern GENERATED AS IDENTITY