In this section, we are going to learn how we can delete the data from the particular table using the Delete command in the PostgreSQL.
The DELETE command is used to delete all existing records from a table. And the WHERE clause is used to remove the selected records or else, all the data would be eliminated.
Note: The TRUNCATE command offers a faster technique to delete all rows from a table.
Syntax
The basic syntax for DELETE command is as follows:
DELETE FROM table_name
WHERE [condition]; The following parameters are used in the above syntax:
| Parameters | Description |
|---|---|
| table_name | It is used to define the table from which we want to delete data in the DELETE FROM clause. |
| Where clause | The WHERE clause is used to describe which record(s) should be deleted. If we ignore the WHERE clause, all records in the table will be deleted. It is an elective parameter. |
The DELETE command returns the number of removed rows. And the Delete command will return zero if no row is removed. Sometime it might not be similar to the number of rows identified by the condition in the WHERE clause since the table might have a BEFORE DELETE trigger. We should be cautious while using the command for deleting records in a table.
In case, we need to verify the condition, which references one or more columns in another table, so for that, we can use the USING clause as below:
DELETE FROM table
USING another_table
WHERE table.id = another_table.id ANDAnd if we don’t need to use the USING clause, we can use the subquery as we can see in the below command:
DELETE FROM table
WHERE table.id = (SELECT id FROM another_table);Example of PostgreSQL Delete command
Here we will take the department and department_tmp tables, which we created in the PostgreSQL insert command section of the PostgreSQL tutorial.
The below command will help us to show the records of the department table:
SELECT * FROM department; 
Example of PostgreSQL DELETE with WHERE clause
The below command is used to delete the rows whose value in the dept_id column is 6:
DELETE FROM department
WHERE dept_id = 6; 
After executing the above command, it will return a message with DELETE 1, which implies that one row has been deleted from the department table.
Note: If we don’t use the WHERE clause, the whole records will be deleted.
And if the department table does not have any row with dept_id 7, then the DELETE command does work, and return as DELETE 0.
For example
Example of PostgreSQL DELETE USING command
If we want to delete all rows from the department table that have values of the dept_id columns in the department_tmp table, we need to follow the following process:
Step1
Firstly, we will see the department_tmp table data with the help of below command:
SELECT * FROM department_tmp; After executing the above command, we will get to see the table structure of department_tmp.
Step2
Then, we will use the below delete command with the help of USING condition to delete the rows in the department table:
DELETE FROM department
USING department_tmp
WHERE
department.dept_id = department_tmp.dept_id;Step3
At last, we will query the department table to check the delete operation is working fine or not.
PostgreSQL deletes all rows from a table
In this, we do not use the WHERE condition in the below DELETE command for deleting all rows from the department table:
DELETE FROM department; As we can see in the below image that the department table now is empty.
After that, we will use the DELETE command’s returning condition for deleting all rows in the department_tmp table and returning the removed rows:
DELETE FROM department_tmp
RETURNING *;
Leave a Reply