How to Use the PostgreSQL Update Statement
If you’re getting started with PostgreSQL, it’s important to learn how to perform basic database operations such as inserting, updating and deleting records. In this tutorial, we’ll focus our attention on the PostgreSQL UPDATE statement, which allows users to modify existing PostgreSQL records. Let’s take a closer look at the
UPDATE statement and review some examples of how it’s used.
Before you attempt to follow along with the examples in this article, make sure that the following prerequisites are in place:
PostgreSQL must be properly installed and working. If you need to confirm whether PostgreSQL is already installed, the command
psql -Vwill display the version of PostgreSQL on your machine.
You’ll need to have a working knowledge of database management systems and basic SQL commands.
Open psql command line interface
Our first task will be to connect to the
psql command-line console.
The following command will grant you the login privileges for the
postgres superuser after you enter the password:
sudo su - postgres
You can then start
Once you’re connected to the psql interface, you’ll be able to execute various PostgreSQL commands and queries.
Create a PostgreSQL database
Before we can update data in a table, we’ll need to create a database. To create it, we’ll use the command shown below:
CREATE DATABASE test;
NOTE: Keep in mind that you can name your database anything you want.
Create a PostgreSQL table
Next, we’ll create a table. This PostgreSQL table will hold the records that we’ll update later on. Here’s the basic syntax for the
CREATE TABLE statement:
CREATE TABLE TABLE_NAME(
COLUMN_NAME + DATATYPE + CONSTRAINTS [OPTIONAL]);
When you create a table in PostgreSQL, you may define many columns separated by commas.
The SQL statement used to create our table will look like the following:
test=# CREATE TABLE grade(
student_id INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
Insert data in a Table
Now that we have a test database and a table, let’s insert some records. The following syntax is used to insert records into a PostgreSQL table:
INSERT INTO TABLE_NAME(column1, column2,....)
INSERT statement will look like this:
INSERT INTO grade(student_id, name, grades, remarks)
VALUES (1, 'Justine', '1.5', 'Pass');
To insert data, we identify the
table_name after the
INSERT INTO clause, followed by the list of columns separated by commas. We then list all the respective values for those columns, again separated by commas.
After inserting data, you can use the
SELECT statement to see all the records in your table:
SELECT * FROM grade;
The results will look like the following:
student_id | name | grades | remarks
1 | Justine | 1.5 | Pass
2 | Mia | 1.25 | Pass
3 | Taylor | 3.5 | Failed
4 | Kobby | 1 | Pass
5 | Kyline | 3 | Failed
Update the PostgreSQL table
UPDATE statement is used to modify existing records in a table. To update specific rows, you need to use the
WHERE clause. Otherwise, every row in the table would be updated.
We use this basic syntax to update a table:
UPDATE TABLE_NAME SET
column1 = 'new_value'
column2 = 'new_value' WHERE [condition];
When you construct an
UPDATE statement, you must first specify the
table_name after the
UPDATE clause. You would then list the columns whose values you want to change under the
SET clause. You can update the values in multiple columns, as long as they’re separated by commas.
Let’s check out an example of the PostgreSQL
UPDATE statement. The following SQL statement will update, or modify, any Postgres records that have a
UPDATE grade SET grades = '2', remarks = 'Pass'
WHERE student_id = 3;
student_id is an ID column with a
PRIMARY KEY constraint, only one record should have been updated:
Let’s verify that our
UPDATE operation was successful:
test=# SELECT name, grades, remarks FROM grade WHERE student_id = 3;
name | grades | remarks
Taylor | 2 | Pass
Update all Postgres table records in a column
It’s also possible to update all of the table rows in a certain column in PostgreSQL. We can do this by passing the column name to the
UPDATE clause, as seen in the following example:.
UPDATE grade SET remarks = 'Pass';
UPDATE statement should return the response shown below, which indicates that five records were updated:
Now that we updated all the records in our table, let’s try executing this query:
SELECT remarks FROM grade;. The result will contain the values in the
remarks column. You’ll see that they’ve all been updated with the value of
When you’re working with data stored in PostgreSQL, you’ll find yourself needing to know how to update existing records in a table. In this article, we discussed the PostgreSQL UPDATE statement and provided multiple examples of its use. With our instructions and examples, you’ll be prepared to modify records in your own PostgreSQL tables.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started