ANSI SQL / UniversalSchema & Admin (DDL)

How to Reset Auto Increment / Sequence in MySQL & PostgreSQL

Reset the next auto-generated primary key ID back to 1 or synchronize it with the maximum existing ID in the table.

Quick SQL Solution

Transactional (DML Update)ANSI SQL / UniversalBeginner

-- MySQL:
ALTER TABLE users AUTO_INCREMENT = 1;

-- PostgreSQL:
SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1)) FROM users;

When to use this scenario:You deleted test rows from a table and want subsequent new rows to start with ID 1, or after a database migration primary key sequence is out of sync.

Step-by-Step Query Breakdown

1MySQL reset auto increment

ALTER TABLE users AUTO_INCREMENT = 1;

Resets the next ID. If table already contains rows, MySQL automatically sets it to MAX(id) + 1.

2PostgreSQL synchronize sequence with MAX(id)

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE(MAX(id), 0) + 1,
  false
) FROM users;

Dynamically looks up the sequence name and sets nextval to maximum existing ID + 1 to avoid duplicate key errors.

3SQLite reset rowid sequence

UPDATE sqlite_sequence SET seq = 0 WHERE name = 'users';

SQLite tracks auto-increment values in the internal sqlite_sequence table.

Critical SQL Pitfalls & Precautions

•In PostgreSQL, restoring data via pg_dump without sequences causes duplicate key violation error code 23505 on next insert until setval() is run.

Frequently Asked Questions

Reset Auto Increment / Sequence - Frequently Asked Questions

Common questions about transactional safety, indexing, and engine compatibility.

Your table sequence was not updated after manual ID inserts. Run the setval() query above to synchronize the sequence with the table MAX(id).

Related SQL Query Guides

Browse All SQL Recipes

ANSI SQL / UniversalSafe

How to Find and Delete Duplicate Rows in SQL

Identify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.

ANSI SQL / UniversalDestructive

DELETE vs TRUNCATE vs DROP in SQL: Differences Explained

Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).

PostgreSQLSafe

How to UPSERT in PostgreSQL Using ON CONFLICT DO UPDATE

Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.

ANSI SQL / UniversalSafe

How to Calculate a Running Total (Cumulative Sum) in SQL

Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).

PostgreSQLSafe

How to Create an Index Concurrently in PostgreSQL (Zero Downtime)

Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.

ANSI SQL / UniversalReversible

How to UPDATE a Table From Another Table in SQL

Update values in one table based on matching columns or aggregations in a secondary table.