PostgreSQL Delete

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:

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:

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:

PostgreSQL Delete

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:

PostgreSQL Delete

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

PostgreSQL Delete

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:

After executing the above command, we will get to see the table structure of department_tmp.

PostgreSQL Delete

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.