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:

DELETE FROM table_name  
WHERE [condition];  

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:

DELETE FROM table
USING another_table
WHERE table.id = another_table.id AND

And 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 *;