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:

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:

As we can see in the below image that the department table now is empty.

PostgreSQL Delete

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:


PostgreSQL Delete




Contact US

Email:[email protected]

PostgreSQL Delete
10/30