SQL RESTART

What does the SQL RESTART keyword do?

Resets a PostgreSQL sequence or identity column to a new starting value, and can reset identity values when truncating tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RESTART: PostgreSQL (supported) Redshift (partial support for ALTER SEQUENCE RESTART) Other major databases (MySQL, SQL Server, Oracle, SQLite) - not supported

SQL RESTART Full Explanation

RESTART is a PostgreSQL reserved keyword used in three contexts: 1. ALTER SEQUENCE - resets an existing sequence generator so the next NEXTVAL call returns a specified or default start value.2. ALTER TABLE ... ALTER COLUMN ... RESTART WITH - resets the internal sequence that backs an IDENTITY column.3. TRUNCATE ... RESTART IDENTITY - empties a table and resets all associated identity/serial sequences so newly inserted rows start from the original or specified seed.Behavior- When WITH value is omitted, the sequence restarts at the START value originally defined for that sequence.- RESTART does not affect other sequence properties such as INCREMENT, MINVALUE, MAXVALUE, or CYCLE.- In TRUNCATE, RESTART IDENTITY applies to the target table and, when CASCADE is specified, also to all tables with foreign-key dependencies.Caveats- RESTART immediately sets the current value; concurrent sessions calling NEXTVAL may see gaps if they cached earlier values.- You need ownership or adequate privileges on the sequence or table to execute RESTART.- RESTART is not part of the ANSI SQL standard; behaviour and availability differ across RDBMSs.

SQL RESTART Syntax

-- 1. Reset a sequence
ALTER SEQUENCE sequence_name RESTART WITH 1;
ALTER SEQUENCE sequence_name RESTART;  -- uses original START value

-- 2. Reset identity column backing sequence
ALTER TABLE table_name ALTER COLUMN column_name RESTART WITH 1000;

-- 3. Truncate and reset identities
TRUNCATE TABLE table_name RESTART IDENTITY;
TRUNCATE table_a, table_b RESTART IDENTITY CASCADE;

SQL RESTART Parameters

sequence_name (identifier) - Name of the sequence to reset
WITH start_value (integer, optional) - First value returned after restart
TABLE_NAME (identifier) - Table containing the identity column or being truncated
COLUMN_NAME (identifier) - Identity column to reset
CASCADE | RESTRICT (keyword, optional) - Propagate truncation to dependent tables or not

Example Queries Using SQL RESTART

-- Example 1: Reset a sequence back to 1
ALTER SEQUENCE user_id_seq RESTART WITH 1;

-- Example 2: After loading historical data, start new IDs at 10,000
ALTER TABLE users ALTER COLUMN id RESTART WITH 10000;

-- Example 3: Empty a table and reset its identity column in one go
TRUNCATE orders RESTART IDENTITY;

Expected Output Using SQL RESTART

The next call to NEXTVAL on user_id_seq returns 1
Future inserts into users
id generate values starting at 10000
After TRUNCATE, the orders table is empty and its identity sequence restarts from its original seed

Use Cases with SQL RESTART

Reloading a table and wanting primary keys to start from a known point
Testing environments where sequences must be reset between test runs
Data migrations or backfills that temporarily advance sequences past desired ranges
Clearing staging tables and ensuring generated keys start cleanly

Common Mistakes with SQL RESTART

Forgetting WITH and assuming RESTART defaults to 1 (it defaults to the sequence's START value)
Attempting RESTART on a sequence without required privileges
Using RESTART in other SQL dialects like MySQL or SQL Server where the keyword is unsupported
Assuming RESTART removes cached sequence values already allocated to other sessions

First Introduced In

PostgreSQL 8.4 (ALTER SEQUENCE RESTART); TRUNCATE ... RESTART IDENTITY added in PostgreSQL 9.1

Frequently Asked Questions

How do I restart a sequence without specifying a value?

Use ALTER SEQUENCE seq_name RESTART; PostgreSQL restarts the sequence at its originally defined START value.

Can I restart an identity column in a single statement?

Yes. ALTER TABLE table_name ALTER COLUMN col_name RESTART WITH n; resets the backing sequence and sets the next generated value to n.

Will RESTART delete data in my table?

No. ALTER SEQUENCE and ALTER TABLE RESTART commands do not touch table data. Only TRUNCATE ... RESTART IDENTITY removes rows.

Is RESTART available in MySQL or SQL Server?

No. MySQL uses ALTER TABLE ... AUTO_INCREMENT = n and SQL Server uses DBCC CHECKIDENT to reseed identity columns. The RESTART keyword is PostgreSQL specific.