Introduction to PostgreSQL TRUNCATE TABLE
PostgreSQL truncate table is used to remove all data from the table, basically, we have used a delete statement to delete data from the table but for a large table, we have used truncate command to delete whole data from a table. Truncate command is delete the whole record from the table also it is faster than delete command, delete command deletes single as well as whole data from the table but truncate is remove all records from the table. Truncate is not used to remove a single record from the table.
Syntax
Below is the syntax of the truncate table statement:
Truncate single table:
Truncate table table_name (Table name which we have used with truncate command);
Truncate single table and reset sequences associated with table column:
Truncate table table_name (Table name which we have used with truncate command) Restart identity;
Truncate multiple tables in angle command:
Truncate table table_name1, table_name2, table_name3, …, table_nameN (Table name which we have used with truncate command);
Remove all data from a table using foreign key references:
Truncate table table_name (Table name which we have used with truncate command) CASCADE;
Truncate table by using all parameter:
Truncate table table_name (Table name which we have used to remove all records) [Restart identity or continue identity] [Cascade or Restrict]
Parameter
Below is the parameter description syntax of the truncate table statement:
Truncate: This statement in PostgreSQL used to remove all records from the table.
Table name: Table name is defined as the name of the table from which we have removed all rows. We can use a single table as well as multiple tables to remove data from the table.
Restart identity: It is defined in truncate table command, using this parameter we can automatically restart the sequences of columns of the table which was we have used in a truncate command.
Continue identity: It is defined in truncate command to show that it does not change the sequence value which was owned by the table column. It is the default value of the table.
Cascade: It is defined as truncate all tables which have foreign key references on another table. Also, drop all the group tables which were added due to cascade.
Restrict: It is defined when we have not to delete tables which were foreign key references.
How does TRUNCATE TABLE work in PostgreSQL?
Below is the working of the truncate table statement in PostgreSQL.
To truncate the table we need to have privileges of the truncate table of the user. If we have not the privileges of the truncate table then we can’t truncate the table. If we have not the permission of the truncate table then it will issue error of “ERROR: permission denied for relation relation_name”.
The below example shows that we need the privilege of a truncate table to truncate the table in PostgreSQL.
Code:
truncate table student;
psql -U postgres
\c testing
truncate table student;
Output:
Explanation: In the above example, tran_test users have not permission to truncate the table so it will issue an error and doesn’t truncate the table. But user Postgres have sufficient privilege of the truncate table, using Postgres user we have truncated the table name as a student. Truncate statement in PostgreSQL will acquire access exclusive lock on the table on which we have performing truncate operations. Basically we have not used a truncate table statement on the table which contained the foreign key references from the other tables, because it will truncate all tables which have foreign key references. Truncate table statement is not used with on delete triggers in PostgreSQL, but we have used truncate triggers. The truncate table statement is not safe with the MVCC architecture. After truncating all record of the table is deleted, and it will appear empty for concurrent transactions.
The truncate table statement is transaction safe in PostgreSQL. Truncate table is rollback the statement if we have not used commit statement after truncate table command. Truncate table statement is performed the same operations as delete statements without where clause. If we have used where clause delete statement will delete the specified row. Truncate table is faster to delete all records from the table. We can truncate single as well as multiple tables in a single command. Basically truncate table does not scan the record to truncate the table, it will delete all rows from a table without scanning. Truncate table statement is to reclaim the storage from the table, we have no need to perform vacuum on the truncated table.
Examples to Implement PostgreSQL TRUNCATE TABLE
Below is the example mentioned:
1. Truncate single table
The below example shows a truncate single table in PostgreSQL. We have a truncated stud_cmp table.
Code:
select * from stud_cmp;
truncate table stud_cmp;
select * from stud_cmp;
Output:
2. Truncate multiple tables
The below example shows the truncate multiple tables in PostgreSQL. We have truncated note, import_testand stud_book table.
Code:
Truncate table note, import_test, stud_book;
select * from note;
select * from import_test;
select * from stud_book;
Output:
3. Truncate single table using restart identity
The below example shows a truncate single table using restart identity in PostgreSQL. We have a truncated stud_test table.
Code:
select * from stud_test;
TRUNCATE TABLE stud_test RESTART IDENTITY;
select * from stud_test;
Output:
4. Truncate single table using cascade
The below example shows a truncate single table using cascade in PostgreSQL. We have a truncated stud_str table.
Code:
select * from stud_str;
TRUNCATE TABLE stud_str RESTART IDENTITY;
select * from stud_str;
Output:
5. Truncate multiple tables using restart identity
The below example shows truncate multiple tables using restart identity in PostgreSQL. We have truncated note, import_test, and stud_book table.
Code:
Truncate table note, import_test, stud_bookRESTART IDENTITY;
select * from note;
select * from import_test;
select * from stud_book;
Output:
Recommended Articles
This is a guide to PostgreSQL TRUNCATE TABLE. Here we discuss an introduction to PostgreSQL TRUNCATE TABLE, syntax, how does it work with programming examples. You can also go through our other related articles to learn more –
