Sequence ID - Reset in Postgresql

When you have played around PostgreSQL tables, have you noticed by default the autoincrement type eg., id is not reset when you delete bunch of records and insert another set of records again, the inserted records get new incremented id not replacing the old ones that were just deleted from table.

To reset the auto-increment ID (or serial sequence) in a PostgreSQL table, you can use the ALTER SEQUENCE command or the SETVAL function. This is typically done after deleting records from the table or truncating it, and you want the next insert to start from a specific ID value (usually 1 or the maximum existing ID + 1).

Here are the steps:

1. Reset to Start from 1

If you want to reset the auto-increment ID to start from 1, you can do this:

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;

Here, replace table_name_id_seq with the actual sequence name. PostgreSQL typically names sequences automatically based on the table and column name (e.g., table_name_id_seq).

Example:

ALTER SEQUENCE blog_posts_id_seq RESTART WITH 1;

1. Reset to Start from the Max ID + 1

If you have deleted some records and want the next inserted record to continue from the current maximum ID in the table, you can do this:

SELECT setval('table_name_id_seq', COALESCE((SELECT MAX(id) FROM table_name), 1), false);

•   The setval function sets the current value of the sequence.
•   The COALESCE function ensures that if there are no rows in the table, the sequence starts from 1.
•   The false flag means the next inserted ID will be MAX(id) + 1.

Example:

SELECT setval('blog_posts_id_seq', COALESCE((SELECT MAX(id) FROM blog_posts), 1), false);

1. Reset After Truncating a Table

If you want to truncate the table (delete all records) and reset the sequence automatically:

TRUNCATE TABLE table_name RESTART IDENTITY;

This will delete all rows and reset the sequence back to 1.

Example:

TRUNCATE TABLE blog_posts RESTART IDENTITY;

Summary:

•   Use ALTER SEQUENCE to manually reset the sequence to a specific value.
•   Use SETVAL to reset the sequence to the maximum existing value + 1.
•   Use TRUNCATE with RESTART IDENTITY to reset the sequence and delete all data.

These commands ensure that your auto-incremented IDs are correctly reset as per your needs in PostgreSQL.