{"id":863,"date":"2024-12-12T16:45:38","date_gmt":"2024-12-12T09:45:38","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=863"},"modified":"2025-01-03T14:35:49","modified_gmt":"2025-01-03T07:35:49","slug":"postgresql-truncate-table","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-truncate-table\/","title":{"rendered":"PostgreSQL TRUNCATE TABLE Statement"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the PostgreSQL TRUNCATE TABLE<\/code> statement to quickly and efficiently remove all data from large tables.<\/p>\n\n\n\n

Getting started with the PostgreSQL TRUNCATE TABLE Statement #<\/a><\/h2>\n\n\n\n

The TRUNCATE TABLE<\/code> statement allows you to remove all rows from a table. Here’s the basic syntax of the TRUNCATE TABLE<\/code> statement:<\/p>\n\n\n

TRUNCATE<\/span> TABLE<\/span> table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you provide the table name you want to truncate in the TRUNCATE TABLE<\/code> clause.<\/p>\n\n\n\n
Note that the TABLE<\/code> keyword is optional, so you can make the statement shorter like this:<\/p>\n\n\n
TRUNCATE<\/span> table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Truncating Table with Identity Columns #<\/a><\/h3>\n\n\n\n
If the table you want to truncate has identity columns<\/a>, you can use the RESTART IDENTITY<\/code> option to restart the values of the identity columns:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name<\/span> \nRESTART<\/span> IDENTITY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, if the value in the identity column starts at 1 with an increment of 1, you can use the RESTART IDENTITY<\/code> option to restart the value back to 1.<\/p>\n\n\n\n
If you don’t want to restart the values of the identity columns, you can use the CONTINUE IDENTITY<\/code> option:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name<\/span> \nCONTINUE<\/span> IDENTITY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the CONTINUE IDENTITY<\/code> option is the default, you can ignore it to use the continuing identity values:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Truncating Table with Foreign Key References #<\/a><\/h3>\n\n\n\n
If you truncate a table with foreign key<\/a> references, you’ll get an error. Fortunately, you can use the CASCADE<\/code> option to automatically truncate the table as well as its foreign key tables in one go:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name<\/span> \nCASCADE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To reject the truncation of the foreign key tables, you can ignore the CASCADE<\/code> option or explicitly use the RESTRICT<\/code> option:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name<\/span> \nRESTRICT<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the RESTRICT<\/code> option is the default.<\/p>\n\n\n\n
If you use the RESTART IDENTITY<\/code> and CASCADE<\/code> options at the same time, you need to place the CASCADE<\/code> option after the RESTART IDENTITY<\/code> option:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name<\/span>\nRESTART<\/span> IDENTITY<\/span>\nCASCADE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL TRUNCATE TABLE Statement Examples #<\/a><\/h2>\n\n\n\n
We’ll use the inventory<\/code> database for demonstration purposes.<\/p>\n\n\n\n
Truncating One Table #<\/a><\/h3>\n\n\n\n
First, use the TRUNCATE TABLE<\/code> to delete all data from the transactions<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> transactions;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, verify the contents of the truncation by counting rows from the transactions<\/code> table:<\/p>\n\n\n
SELECT<\/span> COUNT(*) FROM<\/span> transactions;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 count\n-------\n     0<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Truncating a Table with Foreign Key References #<\/a><\/h3>\n\n\n\n
First, truncate the categories<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> categories;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
ERROR:  cannot truncate a table referenced in a foreign key constraint\nDETAIL:  Table \"products\" references \"categories\".\nHINT:  Truncate table \"products\" at the same time, or use TRUNCATE ... CASCADE.<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns an error because a foreign key constraint in the products<\/code> table references the categories<\/code> table.<\/p>\n\n\n\n
Second, truncate the categories<\/code> table with the CASCADE<\/code> option:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> categories CASCADE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL issued the following notices:<\/p>\n\n\n
NOTICE:  truncate cascades to table \"products\"\nNOTICE:  truncate cascades to table \"product_tags\"\nNOTICE:  truncate cascades to table \"inventories\"\nNOTICE:  truncate cascades to table \"transactions\"<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The TRUNCATE TABLE<\/code> statement truncates the products<\/code> table and has a foreign key reference to the categories<\/code> table.<\/p>\n\n\n\n
Since the products<\/code> table also has foreign key references from the other three tables, product_tags<\/code>, inventories<\/code>, and transactions<\/code>, the TRUNCATE TABLE<\/code> statement also truncates these dependent tables.<\/p>\n\n\n\n
Restarting Identity Option #<\/a><\/h3>\n\n\n\n
The brands<\/code> table has the id<\/code> as the identity column. We’ll truncate this table using the default and the RESTART IDENTITY<\/code> options.<\/p>\n\n\n\n
First, truncate the brands<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> brands CASCADE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert a new row<\/a> into the brands<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> brands(brand_name) \nVALUES<\/span>('Apple'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 brand_id | brand_name\n----------+------------\n       11 | Apple<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL uses the next identity value, which is the default behavior.<\/p>\n\n\n\n
Third, truncate the brands table again with the RESTART IDENTITY<\/code> option:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> brands \nRESTART<\/span> IDENTITY<\/span> CASCADE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 brand_id | brand_name\n----------+------------\n        1 | Apple<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> brands(brand_name) \nVALUES<\/span>('Apple'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The value in the id column starts at 1.<\/p>\n\n\n\n
PostgreSQL TRUNCATE TABLE Statement – Truncating Multiple Tables #<\/a><\/h2>\n\n\n\n
You can truncate multiple tables in one go using the TRUNCATE TABLE<\/code> statement:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table1, table2, ...;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement truncates both users<\/code> and profiles<\/code> tables:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> users, profiles CASCADE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice<\/p>\n\n\n
NOTICE:  truncate cascades to table \"transactions\"<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the transactions<\/code> table has a foreign key reference to the users<\/code> table, the TRUNCATE TABLE<\/code>  statement also truncated the transactions<\/code> table.<\/p>\n\n\n\n
TRUNCATE TABLE vs. DELETE statement #<\/a><\/h2>\n\n\n\n
The DELETE<\/a><\/code> statement without a WHERE<\/a><\/code> clause also allows you to remove all data from a table:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
When the table has a lot of data, the DELETE<\/code> statement is not efficient for the following reasons:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Finally, insert a new row<\/a> into the brands<\/code> table:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n