PostgreSQL Update for an ObjectRocket Instance
Introduction
The PostgreSQL UPDATE function can be used to modify the records in a table that already exists on a PostgreSQL database. The UPDATE statement can be used to update the values stored in a table supported by a specified condition. When used in conjunction with the WHERE clause, the with UPDATE function will only update the selected rows. If the WHERE clause is not used, all of the rows in the table will be modified. This tutorial will explain how to use the PostgreSQL Update for an ObjectRocket instance.
Prerequisites
Access to an established ObjectRocket account.
An instance must be set up on an ObjectRocket account, and a connection established to the PostgreSQL to the ObjectRocket instance, before being able to use the PostgreSQL UPDATE for an ObjectRocket instance.
Setup an Instance on ObjectRocket
Navigate to the create instance tab on an established ObjectRocket account.
Connect the PostgreSQL to ObjectRocket Instance
Following is an example of how to connect PostgreSQL to the ObjectRocket instance:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: Port [5432]: 4144 Username [PostgreSQL]: orkb Password FOR USER orkb: |
On a UNIX-based operating system, such as macOS or Linux, execute the psql command in the terminal window to connect to the PostgreSQL UPDATE for an ObjectRocket instance, as demonstrated in the following example:
1 2 3 4 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \ -U orkb \ -d PostgreSQL \ -p 4144 |
NOTE: The port and ingress URI used in the above example must be changed to match the actual instance. Check the CONNECT tab for the instance in Mission Control to obtain the specific Postgres instance’s URL and port information.
PostgreSQL UPDATE Statement
Following is the syntax for the UPDATE statement used to change the existing value on the table:
1 2 3 | UPDATE TABLE_NAME SET column_name = new_value WHERE condition |
A breakdown of the above syntax follows:
– The name of the table containing the values to be changed must be specified after the UPDATE command.
– The name of the column and the new value must be specified after the SET keyword.
– The WHERE clause is optional and is used only when changing a specific value.
PostgreSQL CREATE TABLE Example
Execute the following CREATE TABLE statement to produce a table to use in the examples in this tutorial:
1 2 3 4 | CREATE TABLE alphabet ( id INT PRIMARY KEY, letter VARCHAR ); |
PostgreSQL INSERT INTO Table
Now insert the following records onto the table;
1 2 | INSERT INTO alphabet (id) VALUES ('1'), ('2'), ('3'), ('4'), ('5'); |
The new table is now set up and ready to use in the below examples.
UPDATE SET PostgreSQL Examples
This first example shows how to use the UPDATE statement in conjunction with the SET statement to change the NULL value of the table:
1 2 | UPDATE alphabet SET letter = 'A'; |
Now view the table using the following SELECT statement:
1 2 3 4 5 6 7 8 9 | SELECT * FROM alphabet; id | letter ----+-------- 1 | A 2 | A 3 | A 4 | A 5 | A (5 ROWS) |
UPDATE Query in PostgreSQL
As shown in the above example, all of the rows in the targeted column where updated. However, the addition of the WHERE clause will result in only specific values being changed. For example, executing the following statement will change the value of the letter where the ID is equal to “two:”
1 2 3 | UPDATE alphabet SET letter = 'B' WHERE id = 2; |
Now check the table again to confirm the update was successful:
1 2 3 4 5 6 7 8 9 | SELECT * FROM alphabet; id | letter ----+-------- 1 | A 3 | A 4 | A 5 | A 2 | B (5 ROWS) |
Note the value has been changed.
PostgreSQL UPDATE WHERE
The UPDATE statement can also be used to modify multiple columns and values by specifying those desired columns or values with the WHERE clause.
Postgres Update Row Example
As shown below, the WHERE clause is being used with the SET clause to update a specific row. However, as there is no way to list and modify all of them automatically, this requires the user to explicitly set every column for the table. Here is an example:
1 2 3 | UPDATE alphabet SET letter = 'Z', id = 10 WHERE id = 5; |
NOTE: Make sure to delimit every column, and the new value, with commas.
Postgres Update Multiple Values
This example shows how to update multiple values by enclosing multiple IDs in parenthesis, using the SQL IN operator, for updating multiple rows with the same value:
1 2 3 | UPDATE alphabet SET letter = 'C' WHERE id IN (3, 4); |
Check the table again to confirm the update was successful:
1 2 3 4 5 6 7 8 9 | SELECT * FROM alphabet; id | letter ----+-------- 1 | A 5 | A 2 | B 3 | C 4 | C (5 ROWS) |
Postgres Update Multiple Columns
This last example will demonstrate how to use the UPDATE WHERE clause to change multiple columns of a table at the same time:
1 2 3 | UPDATE alphabet SET letter = 'C' WHERE id IN (3, 4); |
Note that when using the above SQL statement, all of the row IDs that match the array of the IDs passed to the WHERE clause will have the letter column updated with a new value.
Conclusion
This tutorial explained how to use the PostgreSQL Update for an ObjectRocket instance. The tutorial first explained how to setup an instance on ObjectRocket and how to connect to the instance. The article then covered the PostgreSQL UPDATE statement and provided a breakdown of the UPDATE statement syntax. The tutorial then provided examples for using the PostgreSQL CREATE TABLE, INSERT INTO table, UPDATE SET, the UPDATE query, the UPDATE WHERE clause and both the Postgres update multiple values and multiple columns functions. Remember that when using the PostgreSQL Update for an ObjectRocket instance the port and ingress URI used in the provided example must be changed to match the actual instance.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started