Deleting Data from Table
To delete data from a database table you have to specify at least two things:
- Table where you want to delete data
- Which rows to delete
In order to do so you use the DELETE statements which provided by PostgreSQL as follows:
DELETE FROM [ ONLY ] table_name [ [ AS ] alias ]
[ USING using_list ]
[ WHERE conditions | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ AS output_name]
[, ...] ]
Let's take a look at the DELETE statement in detail.
First is the DELETE FROM followed by the table name where you want to delete data. The table must be exists in the database otherwise PostgreSQL will return an error.
ONLY is an optional part. If you specify ONLY keyword, only records in the table which specify by table name are deleted otherwise all the records from inheriting tables of the table name also are removed (In PostgreSQL you can define tables which inherit from another table)
USING using_list is a list of table expression. It allows column from other table can be referred in the conditions of WHERE clause.
WHERE clause allows you to choose which rows of the table should be deleted. Any row which has values satisfy the conditions of WHERE clause is deleted. If you omit the WHERE clause, all the records of the table are deleted. If you want to delete all the records of a table, PostgreSQL provides you another statement call TRUNCATE TABLE which will be discussed later. This statement allows you to delete all the records of a table faster and more efficiently.
You can specify the name of the cursor to use after WHERE CURRENT OF condition. The records to be deleted is the one most recently fetched from this cursor. It is a mandatory that the cursor must be a simple query on the DELETE's target table. Simple query means non-join or non-aggregate query. Note that a Boolean condition cannot be specified with WHERE CURRENT OF.
output_expression is an expression to be computed and returned by the DELETE statement after each record is deleted. The expression can use any column names of the table_name or table(s) listed in USING. use * if you want to return all columns.
For example if you want to delete all records in the item table you just execute the following query:
DELETE FROM item
To delete specific records you use the WHERE clause with condition. For example if you want to delete only item which has id value is 5 you just perform the following query:
DELETE FROM item
WHERE id = 5
To delete all records in a table, PostgreSQL provides you another statements which is more efficiently than the DELETE TABLE statement. It is TRUNCATE table statement. Basically truncate table statement drop the table first and then recreate a new table with the same table definition. This way is more efficient than the DELETE TABLE statement because it helps database avoid defragmentation of database physical file when the table size is big. Here is the syntax of truncate table statement:
TRUNCATE TABLE table_name
You just have to specify the table name you want to truncate after the TRUNCATE TABLE statement. For example if you want to truncate table item you can execute the following query:
TRUNCATE TABLE item