Whenever we talk about the data storage of any website or application, postgresql comes to our mind. This database supports both JSON and SQL querying. Postgresql is customizable, and you can add many services and plugins you think are not present in postgresql, and you need them for your project. If you want to select specific rows to be deleted, postgresql allows you to use the command having Delete statement with “where” command. We have quoted a sequence of examples here in psql and pgAdmin as well.
Syntax
Condition is either related to any specified column or with another table as well.
Example 1
To delete data from the table, you must have an already created table. Later in the article, we have discussed the method of creating and inserting values in the table in the respective example. Anyways, for now on, consider an example in which we have created a dummy table named software. That contains all the attributes of the software. To display the values of the table, you need a select command with “*”. This symbol implies all data.
As it is displayed that the total number of rows is 6 in the table, we will delete a single row with a constraint. The id with 6 numbers will be deleted when we mention that in the query after “where”. This keyword is used to apply the condition on the specific row or rows of the table. Now apply the query.
This execution will display that a single row is removed from the table according to the given id in the query. To check the position of the table after deletion, we will use the select query again. All the rows are shown except the deleted one.
Example 2
Before going towards some difficult concept. Here are some exemplary situations to create a table, insert values and then apply the delete command having two conditions. Table with “sample1” name is created. Each column name is given with the data type it will use to receive the values. Let us see how it works.
The table will be created. After that, we need to add data to the table through the insert command. This command contains all the values at once. You can also use separate commands to enter the data. In the previous example, we deleted a row using a single condition, but the where clause contains two conditions. The row is specified where the id is 6, and the age is 23.
From the result, you can notice that one row is deleted, and the rest are displayed by using the select statement.
Example 3
You must be familiar with the relation between the two tables. If not! Then this example will escalate your concept regarding the usage of two tables together in a single query. You will see the involvement of the “EXISTS” statement. This statement makes the delete command more complex. Sometimes you encounter such situations when you need to delete the record in one table depending on the other table. You will come to know that the “FROM” clause is not sufficient for this purpose.
This example deals with the usage of two tables in deleting the record. One is a “software” having information about software. Whereas the other one is “system” that contains the id and names of the system. The data of both tables is displayed through the select statement.
Now we will apply the delete command on the table software because a row will be deleted from the table ‘software’ concerning the table ‘system’.
The first position of the command is for the “software” table that is to delete a row from the software. Whereas after the ‘exists’ portion, it is for the table software. This query asks the system to fetch one record where both IDS of tables are the same with a common name. Now navigate back to the tables, and you will find out that in software and system tables, two rows have the same id. Now there is another constraint, constraint of the name. For the same id, there exist two values. So the system will match the name with the one present in the ‘system’ table. When we execute the command, it shows that 2 records are deleted.
If we want to check the data left in both tables, checked through a select statement. You can see that the rows from the ‘software’ table are removed, whereas the ‘system’ table is intact. This means that the ‘system’ table is used only for reference.
Example 4
Whenever we delete any data from the table, only the number of rows that are deleted are displayed but not the row itself. So to preview the specific row, there is an easy method. We only use a “Returning” keyword and “*” at the end of the command. Consider the table as described previously, “system”. Let us see the working.
You can see that the row having 9 ids is deleted and shown as the query’s resultant. Similarly, we have another example in which two rows are selected. These rows are written in a single command in parenthesis. The syntax is a bit different as it involves the addition of “IN” in the command. The command is written as.
The result will be displayed having two ids.
Example 5
Previously we have talked about deleting a specific row from the table. But if you want to remove the whole table named sample1, then we can use the following.
Another way of removing the table is to use the word “drop” with the table name.
Via pgAdmin
The dashboard of postgresql is ‘pgAdmin’. We can also apply the same queries to the pgAdmin. After providing the password, navigate to the left navigation bar, select the database name, and go to the ‘query tool’. Here you can write the query. Show the data of the dummy table ‘sample’ through the select command.
The output portion of the page shows the resultant.
Instead of writing the whole query, you can simply go to the scripts option and select the “delete script” this will automatically display the delete query. You only require to add the id here.
This will display the message that the command is executed, and the execution time is also shown here.
Similarly, you can add the select command for the same table.
Conclusion
This tutorial combines easy and comprehensive examples that show the query of deleting a record from the table. Postgresql allows the user to remove the data from more than one row with the help of the “where” and “AND” conditions.
