PostgreSQL Delete for an ObjectRocket Instance

Introduction

The PostgreSQL DELETE statement can remove either single record or multiple records from a PostgreSQL table. There are instances when managing data in a PostgreSQL database when it may be better to delete only the contents of a table instead of using the DROP TABLE function to remove the entire table from the database. Here the PostgreSQL DELETE clause can be used to remove just the existing records from the table, without deleting the table itself. Additionally, the WHERE clause can be used with the DELETE query to delete targeted data, without removing all of the data contained in the table. This tutorial will explain how to use the PostgreSQL DELETE for an ObjectRocket instance and provide syntax and working examples.

Prerequisites

• The Postgres software must be properly installed and configured on the local machine. The program is available for download here: PostgreSQL 11

• A Postgres instance for an ObjectRocket’s account must be set up via the Mission Control panel. The panel resembles the following screenshot:

-img src=”https://t.gyazo.com/teams/500rockets/23354605b337d6a30fd28e6bac855ad6.png” width=”632″ height=”456″-

• A connection to PostgreSQL on the ObjectRocket instance must be established using the psql utility and the following details:

Alternatively, a connection to PostgreSQL on the ObjectRocket instance can also be established by executing the following command:

PostgreSQL DELETE Statement

The PostgreSQL DELETE statement is used to remove specific existing record(s) from a table.

Executing the WHERE clause in conjunction with the DELETE statement will result in deleting only the specified rows, without removing all of the records from the table.

Following is an example of the DELETE statement syntax:

Here is a breakdown of the syntax:

• First, the DELETE FROM clause is followed by the specific name of the table with the targeted data.

• The optional WHERE clause is followed by the needed criteria to successfully execute the DELETE operation.

Sample Data Set

Following is a table with records that will be used in the examples in this tutorial:

PostgreSQL DELETE With WHERE Clause Example

This section will cover how to delete a specific record with the execution of the WHERE clause.

The following example shows how to delete only those records having a rating lower than three (3):

After executing the above statement, Postgres will display the following:DELETE 1.

Now issue a SELECT statement to verify the results:

Note that the record with an id of two (2) has been successfully deleted from the table.

PostgreSQL DELETE All Records Example

This section will explain how to delete all of the record(s) within the targeted table.

Executing the following statement will delete the entire record within the table online_games:

Note that here the RETURNING clause was used to return all of the deleted records.

The output should look like this:

Conclusion

This tutorial explained how to use the PostgreSQL DELETE for an ObjectRocket instance. The article first covered two different ways of connecting to PostgreSQL on the ObjectRocket instance. The tutorial then explained how to use the PostgreSQL DELETE statement to remove only specific existing record(s) from a table. This was done by executing the WHERE clause in conjunction with the DELETE statement so as not to delete all of the existing records from the table. The tutorial then provided an example and a breakdown of the DELETE statement syntax. The article went on to provided a sample data set used in the examples in the tutorial and then gave examples for executing the PostgreSQL DELETE with WHERE clause and the DELETE all records function. Remember that it is critical to always use the WHERE clause with DELETE queries so as not to delete all of the records from the table when using the PostgreSQL DELETE for an ObjectRocket instance. Jump to top