How to Perform the PostgreSQL UPDATE Query in an ObjectRocket instance
Introduction
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.
Prerequisites
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
A PostgreSQL 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:
1 2 3 | UPDATE TABLE_NAME SET column_name_1 = value_1, column_name_2 = value_2 ,,,. WHERE condition; |
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:
1 | sudo su - PostgreSQL |
Then use the psql command to connect to your ObjectRocket instance:
1 | 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:
1 | CREATE TABLE TABLE_NAME(COLUMN_NAME + DATA_TYPE + CONSTRAINT [OPTIONAL]); |
We’ll use the following command to create our accounts sample table:
1 2 3 4 5 | CREATE TABLE accounts( acc_id SERIAL PRIMARY KEY, acc_name VARCHAR(100), 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:
1 | 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:
1 | SELECT * FROM accounts; |
The expected result would look something like this:
1 2 3 4 5 6 7 8 | 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 acc_id of 4.
We can use the following UPDATE statement to perform this task:
1 | 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 acc_id of 4:
1 | SELECT * FROM accounts WHERE acc_id = 4; |
We can see that our changes have been made, and the appropriate value has been modified:
1 2 3 | acc_id | acc_name | acc_number --------+----------+------------ 4 | Malik | 3210 |
Conclusion
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