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.
The basic syntax for DELETE command is as follows:
The following parameters are used in the above syntax:
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:
And if we don't need to use the USING clause, we can use the subquery as we can see in the below command:
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:
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:
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.
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:
Firstly, we will see the department_tmp table data with the help of below command:
After executing the above command, we will get to see the table structure of department_tmp.
Then, we will use the below delete command with the help of USING condition to delete the rows in the department table:
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:
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: