Postgres Delete Query Syntax Parameters PostgreSQL Delete Query with One Condition PostgreSQL Delete Query with Two Conditions PostgreSQL Delete Query using Exists Condition How To Delete Row in PostgreSQL using pgAdmin
Postgres Delete Query Syntax
The DELETE statement can be defined by the below syntax:
Parameters
with-query: the WITH clause allows us to reference one or more subqueries to be referenced by name in DELETE query. table-name: the name of the table from which records are to be deleted. alias: this is a substitute for the name of the target table. using-list: table expressions to allow columns from other tables to be used in WHERE clause. condition(s): optional. They are the conditions that must be satisfied for records to be deleted. If this section is not provided, all table-name records will be deleted. cursor-name: the cursor to be used in WHERE CURRENT OF condition. The lastly fetched row by this cursor will be deleted. output-expression: the expression to be processed and returned by DELETE statement after the deletion of every row. output-name: the name to be used for the returned column.
Note that since the DELETE statement deletes the entire row, you don’t need to specify the column names.
PostgreSQL Delete Query with One Condition
The DELETE statement can be used with a single condition. The condition is set using the WHERE clause. Consider the Price table with the following data: Price
Let us delete the record with an id of 4: The above command will delete the records in which the id is 4. Let us confirm whether the deletion was successful:
The row with an id of 4 has been deleted.
PostgreSQL Delete Query with Two Conditions
The PostgreSQL DELETE statement can take two conditions. The two conditions should be joined using the AND operator. We will use the following table: Price:
Consider the example given below: In the above command, we are deleting the row in which the id is 3, and price is 300. We can now query the table: This Returns the following:
The record with an id of 3 and a price of 300 was deleted.
PostgreSQL Delete Query using Exists Condition
With the EXISTS condition, you can make the DELETE more complex. Sometimes, there may be a need to delete records in one table based on records in another table. You will see that FROM clause does not allow you to list records from more than one table when performing delete, the EXISTS clause becomes very useful. We have the following two tables: Book:
Price:
We can then run the following query: The above command will delete from the Book table where there exists a record in the Price table with an id matching that of the Book table and the price being less than 250. The Book table is now as follows:
The record with an id of 1 was deleted.
How To Delete Row in PostgreSQL using pgAdmin
Following are the steps to delete a row in PostgreSQL using pgAdmin:
With One condition
To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account Open pgAdmin and Login to your account using your credentials Step 2) Create a Demo Database
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the Query Type the below query in the query editor: Step 4) Execute the Query Click the Execute button
Step 5) Check if the Row is Deleted Let us check whether the deletion was successful:
With Two conditions
To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account. Step 2)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
Step 5) Let us check whether the deletion was successful:
Using EXISTS Condition
To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account. Step 2)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
Step 5) Let us check whether the deletion was successful:
Summary
The DELETE statement is used for deleting one or more records from a table. To delete only select rows from a table, you can combine the DELETE statement with the WHERE clause. If the DELETE clause is used without the WHERE clause, it deletes all records from the table. table-name parameter allows you to add the name of the table from which records are to be deleted. We can use the DELETE statement with one condition, specified using the WHERE clause. The DELETE statement can also be used with two conditions specified in the WHERE clause. The two conditions should be joined using the AND operator. The EXISTS condition can help us delete records from a table based on the records of another table.
Download the Database used in this Tutorial
