How to Perform the PostgreSQL UPDATE Query in an ObjectRocket instance
If you have data stored in PostgreSQL tables, there’s a good chance you’ll need to update the contents of those tables at some point. The PostgreSQL
UPDATE statement makes it easy to perform any modifications you might need. In this tutorial, we’ll discuss this statement in detail and check out some examples of how to perform a PostgreSQL update query in an ObjectRocket instance.
Let’s begin by reviewing a few requirements that need to be in place for this tutorial:
You’ll need to have an account on ObjectRocket. You can visit the control panel web page to create a PostgreSQL instance.
The installed PostgreSQL database cluster on your device must be version 12.2 or later to ensure that it supports the PostgreSQL instance in ObjectRocket.
PostgreSQL UPDATE Query
UPDATE query is used to modify existing table records in a database. You can also use a
WHERE clause to update only selected rows. Without a
WHERE clause, all of the rows in the table will be updated.
The syntax for an
UPDATE query is:
UPDATE TABLE_NAME SET column_name_1 = value_1,
column_name_2 = value_2 ,,,.
Accessing the Psql Interface in an ObjectRocket Instance
To access the PostgreSQL interface in ObjectRocket, click the VIEW MORE DETAILS button. Then, in the CONNECT tab, copy your credentials in the psql command-line interface. Make sure that you created your own user with a strong password.
Once you’ve completed these steps, open your terminal and access the PostgreSQL interactive shell by using the command:
sudo su - PostgreSQL
Then use the
psql command to connect to your ObjectRocket instance:
psql -U username -h localhost_name -p port
NOTE: If you’re not sure where to find your localhost name, it’s located at the same tab after the “@” sign.
Use PostgreSQL to Create a Table
Let’s create a sample table with some records that we can modify in our examples. The syntax for the
CREATE TABLE statement is:
CREATE TABLE TABLE_NAME(COLUMN_NAME + DATA_TYPE + CONSTRAINT [OPTIONAL]);
We’ll use the following command to create our
accounts sample table:
CREATE TABLE accounts(
acc_id SERIAL PRIMARY KEY,
acc_number INT NOT NULL
Then we’ll use the
INSERT statement to add a few records that we can update later.
To insert records in a table, we use the syntax shown below:
INSERT INTO TABLE_NAME(COLUMN_NAME) VALUES(VALUES);
After we insert some sample records, we can use a
SELECT statement to display the records in our table:
SELECT * FROM accounts;
The expected result would look something like this:
acc_id | acc_name | acc_number
1 | Margaret | 2374
2 | David | 6524
3 | Wayne | 4255
4 | Malik | 3125
5 | Angela | 1234
6 | Odette | 3657
Use the PostgreSQL to Update Record of a Table
Now that we have our own sample table that contains some records, let’s try to modify the
acc_number of the record that has an
We can use the following
UPDATE statement to perform this task:
UPDATE accounts SET acc_number = 3210 WHERE acc_id = 4;
Next, let’s verify that our update query was successful by selecting the record with an
SELECT * FROM accounts WHERE acc_id = 4;
We can see that our changes have been made, and the appropriate value has been modified:
acc_id | acc_name | acc_number
4 | Malik | 3210
Being able to update existing records is an important part of managing data in PostgreSQL. The
UPDATE statement makes it easy to modify PostgreSQL table data; including a
WHERE clause allows you to place conditions on which records get updated. In this tutorial, we discussed the syntax of the
UPDATE statement and walked you through an example of a PostgreSQL UPDATE query in an ObjectRocket instance. If you’ve been able to follow along with our instructions and examples, you’ll be ready to update tables in your own PostgreSQL environment.
Jump to top
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started