is there a way to reset the primary key of a postgres table to start at 1 again on a populated table?
right now its generating numbers from 1000000 and up. I want it all to reset and start to 1, keeping all my existing data intact.
Thanks.
+2
A:
Primary keys that autoincrement (i.e., columns with data type serial primary key) are associated with a sequence. You can set the next value for any sequence using setval(<seqname>,<next_value>).
The name of the auto created sequences when using serial are <table>_<column>_seq
Vinko Vrsalovic
2010-09-29 06:41:53
+1
A:
What's the point? It's just a number without any meaning. You could update all records and all related data in all other tables. You also have to reset all sequences.
Be careful with your backups as well, older backups hold very different data!
Don't waste your time getting nowhere.
Frank Heikens
2010-09-29 06:42:08
There's no need to update anything, just reset the sequences and the data will be kept intact. I agree that for production databases this makes little sense, but for development/staging it might help debugging or with similar tasks.
Vinko Vrsalovic
2010-09-29 08:13:13
No need? It is a a populated table, just a reset of the sequence is not going to work, you will have many conflicts.
Frank Heikens
2010-09-29 08:18:24
@Frank: Depends on the data. If you have a gap from 1 to 1000000 it will work for a while
Vinko Vrsalovic
2010-09-29 11:28:29
In fact I had understood that he had such a gap, rereading the question that might not be the case.
Vinko Vrsalovic
2010-09-29 11:37:37