DELETE, TRUNCATE and DROP commands are 3 SQL commands used to clean data; which can be used based on our requirements; Below is small comparative explanation of the same.
DELETE:
1)DELETE can be used to delete any specific row using WHERE clause or It can be used to delete all the records in a table.
Syntax:
DELETE FROM tables_name WHERE condition;
2)It is a DML (data manipulation language) command.
3)This is more of a row level and while transaction puts lock on row which is getting deleted.
4)It is slower compared to truncate operation since it uses more transaction space.
5)Permission wise user only needs to have delete permission (since it is a DML).
6)Delete can activate TRIGGER since it is row level.
Example for DELETE: in below snap copy from postgre shell, we have a table called email_rep, in which one record was deleted with condition id=1 after delete command we see only that specific record has been deleted;
test_1=# SELECT * FROM email_rep;
id | email
----+----------------------------
1 | [email protected]
2 | [email protected]
3 | [email protected]
4 | [email protected]
5 | [email protected]
(5 rows)
test_1=# DELETE FROM email_rep WHERE id=1;
DELETE 1
test_1=#
test_1=# SELECT * FROM email_rep;
id | email
----+----------------------------
2 | [email protected]
3 | [email protected]
4 | [email protected]
5 | [email protected]
(4 rows)
TRUNCATE:
1)truncate removes all the rows in the table at one shot
Syntax:
TRUNCATE TABLE table_name;
2)It is a DDL (data definition language) command.
3)This is a table level operation hence it puts lock on table while the transaction is in progress.
4)Faster compared to delete since the transaction space used is less.
5)Need alter table permission (since it is a DDL).
6)Truncate can not activate trigger since it is an operation at table level.
Example for TRUNCATE: In below example we have performed truncate on same table; after truncate operation we can see no rows being returned for select statement
test_1=# TRUNCATE TABLE email_rep;
TRUNCATE TABLE
test_1=# SELECT * FROM email_rep;
id | email
----+-------
(0 rows)then what is DROP?
Drop is a command used to delete entire table including table itself, means it cleans both data and table.
syntax:
DROP TABLE table_name;
DROP can be used to drop entire database syntax of it is as follows
DROP DATABASE database_name;
example for DROP: below copy from psql shell shows example of DROP operation on table and database
test_1=# DROP TABLE email_rep;
DROP TABLE
test_1=# SELECT * FROM email_rep;
ERROR: relation "email_rep" does not exist
LINE 1: SELECT * FROM email_rep;
^
test_1=# DROP DATABASE test_1;
ERROR: cannot drop the currently open database
test_1=#
test_1=# \c project_1
You are now connected to database "project_1" as user "postgres".
project_1=# DROP DATABASE test_1;
DROP DATABASE
project_1=#
project_1=#
project_1=# \c test_1
FATAL: database "test_1" does not exist
Previous connection kept
project_1=#