How to Use the PostgreSQL Update Statement

Introduction

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.

Prerequisites

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 -V will 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:

You can then start psql:

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:

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:

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:

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:

Our INSERT statement will look like this:

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:

The results will look like the following:

Update the PostgreSQL table

The PostgreSQL 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:

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 student_id of 3:

Since 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:

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:.

This 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 Pass:

Conclusion

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.