intro
Let’s dive into the PostgreSQL truncate table reset sequence issue and how to clear the values of sequences when clearing table data!
Truncating a table does not completely reset it. Specifically, the Postgres TRUNCATE command does not affect the values of table sequences. To reset the sequences, you need to follow a specific PostgreSQL truncate table reset sequence approach. Follow this article to learn how to do it!
What Is a PostgreSQL Sequence?
As covered in our blog post, a sequence is essentially a database object that generates numbers. Postgres uses sequences to produce a series of progressive numbers, which is typically used in auto-incremental numeric primary keys.
For those familiar with MySQL, sequences in PostgreSQL function similarly to the AUTO_INCREMENT behavior. However, a key distinction is that PostgreSQL sequences can be configured to start from a specified value and decrement with each INSERT, if desired. In contrast, AUTO_INCREMENT in MySQL only increments the value automatically by one unit.
You can create a sequence in PostgreSQL using the CREATE SEQUENCE statement. Alternatively, the special data type SERIAL sets up an auto-incrementing numeric primary key by employing a sequence under the hood. Find out more in your guide on PostgreSQL data types.
The Effect of TRUNCATE on Sequences
In most cases, a TRUNCATE command for a single table looks like this:
1
TRUNCATE table_name;
Or, if you are truncating multiple tables at once, it may appear as follows:
1
TRUNCATE table_1, table_2;
But do not forget that the PostgreSQL TRUNCATE command offers additional options. Here is its complete syntax:
1
TRUNCATE [ TABLE ] [ ONLY ] table_1 [, ..., table_n]
2
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
CASCADE truncates all tables that have foreign-key references to any of the specified tables or any tables included through CASCADE. Instead, RESTRICT prevents truncation if any of the specified tables have foreign-key references from tables not included in the command. RESTRICT is the default behavior.
For this article, what matters are the [RESTART IDENTITY | CONTINUE IDENTITY] options:
So, consider this query:
1
TRUNCATE table_name;
When you launch it, PostgreSQL is actually running:
1
TRUNCATE table_name CONTINUE IDENTITY RESTRICT;
Thus, Postgres executes the TRUNCATE command without modifying the sequence values by default. Let’s confirm this behavior with an example.
Example
Suppose you have a products table that contains the following data:
As you can see in a visual PostgreSQL database client like DbVisualizer, the table only contains 8 products.
Also, take a look at its SQL DDL definition:
Note how the column id is of type SERIAL, which means it is associated with a sequence. Thanks to DbVisualizer Pro, you can explore a table DDL with a single click.
Now, launch the TRUNCATE query:
1
TRUNCATE products;
The table will now be empty:
Add a new product with the following INSERT query:
1
INSERT INTO products (name, description, price) VALUES ('Noise-Cancelling Headphones', 'Over-ear headphones with active noise cancellation and 30-hour battery life.', 249.99);
Since TRUNCATE did not reset the id sequence, the new product will have 9 (the next number) as its ID. Verify that with a simple SELECT * query:
1
SELECT * FROM products;
The result will be:
Notice how the id column has value 9. Behavior confirmed!
PostgreSQL Truncate Table Reset Sequence Instructions
As it should now be clear, the PostgreSQL truncate table reset sequence trick is to specify the RESTART IDENTITY option in the TRUNCATE query:
1
TRUNCATE table_name RESTART IDENTITY;
Let’s verify that it works!
Example
Suppose you are dealing again with a populated products table:
Now, execute the PostgreSQL TRUNCATE query to reset the sequence:
1
TRUNCATE products RESTART IDENTITY;
Next, insert a new product:
1
INSERT INTO products (name, description, price) VALUES ('Noise-Cancelling Headphones', 'Over-ear headphones with active noise cancellation and 30-hour battery life.', 249.99);
This time, the new product will have an id of 1, as the sequence has been reset:
Et voilà! You have just learned how to truncate a table and reset sequences in PostgreSQL!
Conclusion
In this guide, you learned what a PostgreSQL sequence is and why the TRUNCATE command does not reset it by default. You then explored a simple yet effective PostgreSQL truncate table reset sequence approach.
As shown here, DbVisualizer greatly simplifies query execution, data exploration, and table definition discovery. This full-featured visual database client supports various DBMS technologies and offers advanced capabilities such as query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
How do you truncate tables and reset sequences in PostgreSQL?
By default, the TRUNCATE command in PostgreSQL does not reset the values of sequences. To modify this behavior, you can use the command with the following syntax:
1
TRUNCATE table_name RESTART IDENTITY;
The RESTART IDENTITY option instructs PostgreSQL to reset the sequences associated with the columns of the truncated table.
What are some scenarios where PostgreSQL can go out of sync?
The main reasons a PostgreSQL sequence can go out of sync are:
How do you fix sequences that went out of sync in PostgreSQL?
To fix all out-of-sync sequences, run the following query:
1
SELECT 'SELECT SETVAL(' ||
2
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
3
', COALESCE(MAX(' || quote_ident(C.attname) || '), 1)) FROM ' ||
4
quote_ident(PGT.schemaname) || '.' || quote_ident(T.relname) || ';'
5
FROM pg_class AS S
6
JOIN pg_depend AS D ON S.oid = D.objid
7
JOIN pg_class AS T ON D.refobjid = T.oid
8
JOIN pg_attribute AS C ON D.refobjid = C.attrelid AND D.refobjsubid = C.attnum
9
JOIN pg_tables AS PGT ON T.relname = PGT.tablename
10
WHERE S.relkind = 'S'
11
ORDER BY S.relname;
This query generates the necessary SETVAL commands to update all sequences based on the maximum values of the corresponding columns. Learn more about how to fix a sequence when it goes out of sync in PostgreSQL.
How can you retain sequence values after a TRUNCATE instruction in PostgreSQL?
By default, TRUNCATE retains the current values of sequences. To explicitly enforce that behavior, you need to use the CONTINUE IDENTITY option:
1
TRUNCATE table_name CONTINUE IDENTITY;
This ensures that sequences associated with the truncated table(s) are not reset.
Why should you use a database client?
Using an SQL client like DbVisualizer allows you to visually deal with data in your databases. A powerful SQL client provides a range of tools that simplify data management and analysis, no matter which database management system you are using. These features enhance productivity, streamline workflows, and help you gain insights from your data more effectively.
