How to Use the Postgres UPDATE WHERE Statement
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.
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 WHEREclause. If you’re using a
systemddistribution of Linux, you can use the
systemctl status postgresqlcommand to verify that PostgreSQL is indeed installed and running. You can use
psql -Vto 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.
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:
sudo su - postgres
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
We’ll use this command to create a database in
CREATE DATABASE employee_db;
The command shown above should return a response of
CREATE DATABASE if it was executed successfully. You can then use the
\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:
CREATE TABLE employees
(employee_id INT NOT NULL PRIMARY KEY, name VARCHAR (150), STATUS VARCHAR(150), salary REAL);
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:
INSERT INTO employees (employee_id, name, STATUS, salary)
VALUES (1, 'Georgia', 'Contractual', '50000'),
(2, 'George', 'Salary', '80000'),
(3, 'Jorge', 'Contractual', '60000');
This SQL statement will insert three records into 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:
SELECT * FROM employees;
Let’s check out the results of that
SELECT query, assuming that the records were indeed inserted into it:
employee_db=# select * from employees;
employee_id | name | status | salary
1 | Georgia | Contractual | 50000
2 | George | Salary | 80000
3 | Jorge | Contractual | 60000
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:
UPDATE employees SET STATUS = 'Salary';
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:
SET some_col = 'NEW VALUE GOES HERE!'
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
To execute an update statement that contains a
WHERE clause, use the following syntax:
UPDATE TABLE_NAME SET column1 = value1, column2 = value2....
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”:
SET name = 'George Costanza'
WHERE employee_id = 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:
SELECT * FROM employees WHERE employee_id = 2;
This SQL statement should now return a table of results that looks like the following:
employee_db=# SELECT * FROM employees WHERE employee_id = 2; employee_id | name | STATUS | salary
2 | George Costanza | Salary | 80000
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
status columns for each record where the status is currently set to
Contractual. This will give the employees both a promotion and a raise:
SET salary = 100000, STATUS = 'Salary'
WHERE STATUS = 'Contractual';
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'.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started