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!

Erwin Brandstetter · Accepted Answer · 2021-05-23 23:26:01Z

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

Ryan · Accepted Answer · 2022-09-01 00:53:34Z

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:

'table' = your table name
'id' = your id column name

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

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

Collectives™ on Stack Overflow

How to reset the auto generated primary key in PostgreSQL

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related