Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Truncate Table

Truncate command removes all records from a table. But this command will not destroy the table's structure. When we apply truncate command on a table its Primary key is initialized.
truncate table table-name;
For example
postgres=# select * from item;
 item_no |  name  | cost 
       1 | laptop |   20
       2 | pen    |   10
       3 | paper  |    8
(3 rows)
--The below query will delete all the records of Student table.
postgres=# truncate table item;
--Table Structure(meta data) can't be delete ,its delete only records not metadata
postgres=# select * from item; 
 item_no | name | cost 
(0 rows)
Difference between truncate and Delete command:
Delete command will delete all the rows from a table whereas truncate command re-initializes a table(like a newly created table) also operation will be done quickly compare to delete.

For eg. If you have a table with 10 rows and an auto_increment primary key, if you use delete command to delete all the rows, it will delete all the rows, but will not initialize the primary key, hence if you will insert any row after using delete command, the auto_increment primary key will start from 11. But in case of truncate command, primary key is re-initialized.


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

spfile and pfile errors

2017 Shortern URL - Other Website Like ADFOC.US

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory