PostgreSQL – How to Delete Data from a Table
To delete the data from a table in PostgreSQL, you can use the DELETE statement without a WHERE clause. This will remove all rows from the table, effectively emptying it. Below is the basic syntax:
DELETE FROM table_name;
where table_name is the name of the table you want to delete the data from. After running the DELETE statement, all rows in the table will be removed, effectively emptying the table.
Exercise caution when using the DELETE statement without a WHERE clause, as it will delete all rows in the specified table, and there’s no way to recover them unless you have a backup. Make sure you have a backup or are absolutely certain that you want to empty the table before executing this command in a production environment.
You can also use the TRUNCATE statement to remove all rows from a table. Here’s the basic syntax:
Truncate table table_name;
TRUNCATE is a fast operation because it deallocates the space used by the table and its associated indexes. However, it cannot be rolled back by a transaction, and the data removed by TRUNCATE is not recoverable unless you have a backup. Therefore, be cautious when using TRUNCATE in a production environment and ensure you have appropriate backups in place.
In PostgreSQL, DELETE and TRUNCATE are two different SQL statements used for removing data from tables, but they have distinct purposes and behaviors.
DELETE:
DELETEis a Data Manipulation Language (DML) statement.- It is used to remove specific rows from a table based on a specified condition using a
WHEREclause. You can delete specific rows that meet certain criteria. - It is a logged operation, meaning it generates individual entries in the transaction log for each row deleted. This can be useful for audit purposes but can also make
DELETEslower for large datasets. - Deleted rows can be rolled back if the transaction is not committed.
TRUNCATE:
TRUNCATEis a Data Definition Language (DDL) statement.- It is used to remove all rows from a table, effectively emptying the entire table. You cannot specify a
WHEREclause withTRUNCATE. - It is a minimally logged operation. Instead of generating individual log entries for each deleted row, it deallocates the space used by the table and its associated indexes. This makes
TRUNCATEfaster thanDELETE, especially for large tables. TRUNCATEcannot be rolled back by a transaction. Once executed, the data is gone.