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:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: <your_database_name> Port [5432]: 4149 Username [PostgreSQL]: pguser Password FOR USER orkb: <your_password> |
Alternatively, a connection to PostgreSQL on the ObjectRocket instance can also be established by executing the following command:
1 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149 |
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:
1 2 | DELETE FROM <table_name> WHERE <criteria> |
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 theDELETE
operation.
Sample Data Set
Following is a table with records that will be used in the examples in this tutorial:
1 2 3 4 5 6 7 8 9 10 11 12 13 | id | title | category | rating ----+---------------------+------------------+-------- 1 | lord of the rings | board games | 3 2 | blokus | board games | 2 3 | mortal comabat | arcade games | 4 4 | tekken tag | arcade games | 4 5 | starcraft | strategy games | 5 6 | warcraft | strategy games | 5 7 | Team Fortress | FPS games | 4 8 | Halo | FPS games | 5 9 | Sims 2 | simulation games | 4 10 | farmer simulator 17 | simulation games | 3 (10 rows) |
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):
1 | DELETE FROM online_games WHERE rating - 3; |
After executing the above statement, Postgres will display the following:DELETE 1
.
Now issue a SELECT
statement to verify the results:
1 2 3 4 5 6 7 8 9 10 11 12 | sandbox=> SELECT * FROM online_games; id | title | category | rating ----+---------------------+------------------+-------- 1 | lord OF the rings | board games | 3 3 | mortal comabat | arcade games | 4 4 | tekken tag | arcade games | 4 5 | starcraft | strategy games | 5 6 | warcraft | strategy games | 5 7 | Team Fortress | FPS games | 4 8 | Halo | FPS games | 5 9 | Sims 2 | simulation games | 4 10 | farmer simulator 17 | simulation games | 3 |
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
:
1 | DELETE FROM online_games RETURNING *; |
Note that here the RETURNING
clause was used to return all of the deleted records.
The output should look like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | sandbox=> DELETE FROM online_games RETURNING *; id | title | category | rating ----+---------------------+------------------+-------- 1 | lord of the rings | board games | 3 3 | mortal comabat | arcade games | 4 4 | tekken tag | arcade games | 4 5 | starcraft | strategy games | 5 6 | warcraft | strategy games | 5 7 | Team Fortress | FPS games | 4 8 | Halo | FPS games | 5 9 | Sims 2 | simulation games | 4 10 | farmer simulator 17 | simulation games | 3 (9 rows) DELETE 9 |
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
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started