How to reset the auto generated primary key in PostgreSQL

Question

My class for the table topics is as below. The primary key is autogenerated serial key. While testing, I deleted rows from the table and was trying to re-insert them again. The UUID is not getting reset.

class Topics(db.Model):
    """ User Model for different topics """
    __tablename__ = 'topics'

    uuid = db.Column(db.Integer, primary_key=True)
    topics_name = db.Column(db.String(256),index=True)

    def __repr__(self):
        return '<Post %r>' % self.topics_name

I tried the below command to reset the key

ALTER SEQUENCE topics_uuid_seq RESTART WITH 1;

It did not work.
I would appreciate any form of suggestion!

Answer 1

If it's indeed a serial ID, you can reset the owned SEQUENCE with:

SELECT setval(pg_get_serial_sequence('topics', 'uuid'), max(uuid)) FROM topics;

See:

How to reset postgres' primary key sequence when it falls out of sync?

But why would the name be uuid? UUID are not integer numbers and not serial. Also, it's not entirely clear what's going wrong, when you write:

The UUID is not getting reset.

About ALTER SEQUENCE ... RESTART:

• Postgres manually alter sequence

Answer 2

In order to avoid duplicate id errors that may arise when resetting the sequence try:

UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;

For added context:

1. 'table' = your table name

2. 'id' = your id column name

3. 'seq' = find the name of your sequence with:

   SELECT pg_get_serial_sequence('table', 'id');