I am uploading 64 features from 64 shapefiles into the postgis server (I use INSERT TO to append them one by one), for some reasons I made some mistakes at the first attempt so I deleted them (except where id = 1), then I re-upload them. However, the id (primary key generated automatically when uploading) does not start from 2 but from 65.

Why is postgis doing this? How do I change it back to start from 2? Also is there any way that I can arrange the table by the id in an ascending order (so that when the user drag it into QGIS and open the attribute table, they can see that the features are arranged ascendingly)?


1 Answer 1


You could try the ALTER SEQUENCE sequence RESTART; command before the re-insert.

Also has the option to restart the sequence from a specified value like ALTER SEQUENCE sequence RESTART WITH 1;

Where sequence is the name of the sequence associated with that table.

As another option, if you want to empty an entire table but reset the sequence this can be done with the TRUNCATE command like TRUNCATE table RESTART IDENTITY; where table is the name of the target table being emptied and RESTART IDENTITY resets the sequence so that when the table is re-populated the sequence starts back at the lowest value.

For further reading have a look at ALTER SEQUENCE and TRUNCATE docs from postgresql.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.