PostgreSQL Update for an ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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 for ObjectRocket example using update query in PostgreSQL

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 row example for the PostgreSQL UPDATE in ObjectRocket tutorial

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.