How to Use the Postgres UPDATE WHERE Statement

Introduction

When you need to change the value of existing records in PostgreSQL, the UPDATE statement provides a simple way to get the job done. However, without any filtering criteria in place, an UPDATE statement will modify all records in the table. That’s where the WHERE clause comes into play. You can add this clause to your UPDATE statement to filter the records and extract the ones that meet a certain condition. In this article, we’ll show you how to update specific columns in selected records using the Postgres UPDATE WHERE clause in psql.

Prerequisites

There are a couple of important prerequisites that need to be in place in order to follow along with the examples in this article:

• You’ll need to have PostgreSQL installed on your computer to try any examples of the PostgreSQL UPDATE WHERE clause. If you’re using a systemd distribution of Linux, you can use the systemctl status postgresql command to verify that PostgreSQL is indeed installed and running. You can use psql -V to return the version of the command-line interface installed on your machine.

• You’ll also need to have a basic working knowledge of PostgreSQL, and you should be somewhat familiar with SQL statements and their basic syntax as well.

Access psql

Our first task will be to access the psql interactive terminal so that we can execute SQL commands in PostgreSQL. We’ll use the command below:

After entering the password for the postgres user, you should have the privileges needed to execute the psql command and access the interface.

Create a database for Postgres

Now that we’ve managed to access psql, let’s create a database and a table with some records in it. Later on in this tutorial, we’ll be updating some of the values in that table using the WHERE clause.

We’ll use this command to create a database in psql:

The command shown above should return a response of CREATE DATABASE if it was executed successfully. You can then use the \conn or \c command, followed by the database name, to connect to it.

PostgreSQL ‘CREATE TABLE’ statement

We’ll use the following CREATE TABLE statement to create a simple Postgres table in psql for our examples:

PostgreSQL ‘INSERT INTO’ example

After creating our new table, we’ll insert records into it using the INSERT INTO SQL keywords.

Here’s the SQL statement we’ll use to insert records into a Postgres table. We’ll need these records later in order to test the UPDATE WHERE SQL statement:

This SQL statement will insert three records into the employees table.

The INSERT INTO statement allows you to add new rows to a Postgres table. When you use this statement, make sure you place the table name (employees) after the INSERT INTO keywords. You should also make sure to enclose each record in parentheses and delimit them using commas.

PostgreSQL ‘SELECT’ example

To verify that the data was successfully inserted, we can use the SELECT statement shown below:

Let’s check out the results of that SELECT query, assuming that the records were indeed inserted into it:

PostgreSQL ‘UPDATE’ example

Our first example will show how to update all the values for each record in a particular column. Let’s say we wanted to change all the values in the status column to Salary— we could execute the following SQL statement in psql:

NOTE: You’ll need to use the SET clause to let Postgres know which column values you’d like to change.

Postgres ‘UPDATE WHERE’

The syntax of an UPDATE statement changes a bit when you add a WHERE clause. The statement still changes values, just like it did in the example above, but it will first check for records that match a particular condition.

The basic syntax for the UPDATE-SET-WHERE SQL statement is shown below:

Let’s take a closer look at this statement. First, we specify the name of the table that we want to update. Then we use the SET clause to list the names of the columns we want to update. You can update the values of multiple columns in a single UPDATE statement– just use a comma to separate the columns.

The last part of the statement includes the WHERE clause to define the condition which specifies the rows we’d like to update.

Update the PostgreSQL table

We just looked at an example of how to update the value of specified rows in our sample table. Notice that it contained a WHERE clause. In an update statement, the WHERE clause defines the condition which a row in a table must meet in order to be updated. Without this clause, all rows will be updated and their respective values will be changed to the value passed to SET.

To execute an update statement that contains a WHERE clause, use the following syntax:

PostgreSQL update table column example

If you want to update just one column for only one record, the safest way to do it is by specifying the record’s ID in the WHERE clause. Keep in mind that this method will only work if the column has a UNIQUE constraint applied to it.

In the following example, we’ll use the SET clause to append a last name to the employee’s name column. We’ll be making this change to the record that has an ID of “2”:

If the update is successful, the above statement should output UPDATE 1, indicating that one record was updated. You can use the following SELECT statement to verify that the record’s name value was indeed updated:

This SQL statement should now return a table of results that looks like the following:

PostgreSQL update multiple columns example

If you want to update values for multiple columns using the UPDATE WHERE SQL clause, you can do so by passing multiple values to SET, delimited by commas.

Let’s have some fun in our next example and give everyone a raise. We’ll change the salary and status columns for each record where the status is currently set to Contractual. This will give the employees both a promotion and a raise:

The result of this SQL statement will show that it updated two columns’ values for certain records in the table.

It’s easy to see why the WHERE clause is so important– it prevents all of the records from being altered. In the absence of the conditions specified in the WHERE clause, all of the table’s records will be updated.

NOTE: Be sure to use single quotes instead of double quotation mark) whenever you use strings in SQL. To escape a string in order to use characters such as apostrophes in it, simply use two single quotes in a row. An example of this would be: SET name = 'Johnson''s Bakery'.

Conclusion

When you’re working with PostgreSQL data, it’s important to know how to perform UPDATE operations correctly. In this article, we’ve covered several examples of how to use the UPDATE WHERE clause to modify Postgres records. If you’d like to update records so that your changes are reversible, you’ll need to perform your update as a transaction and roll back the transaction before it’s committed. Otherwise, it’s best to use a sample table to experiment with the power of the UPDATE statement. Using the examples in this tutorial as a guide, you’ll be prepared to perform UPDATE WHERE statements in your own PostgreSQL environment.