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 asystemd
distribution of Linux, you can use thesystemctl status postgresql
command to verify that PostgreSQL is indeed installed and running. You can usepsql -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:
1 | 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 WHERE
clause.
We’ll use this command to create a database in psql
:
1 | 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 \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:
1 2 | 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:
1 2 3 4 | 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 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:
1 | SELECT * FROM employees; |
Let’s check out the results of that SELECT
query, assuming that the records were indeed inserted into it:
1 2 3 4 5 6 7 | employee_db=# select * from employees; employee_id | name | status | salary -------------+---------+-------------+-------- 1 | Georgia | Contractual | 50000 2 | George | Salary | 80000 3 | Jorge | Contractual | 60000 (3 rows) |
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:
1 | 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:
1 2 3 | UPDATE some_table SET some_col = 'NEW VALUE GOES HERE!' WHERE some_condition; |
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:
1 2 | UPDATE TABLE_NAME SET column1 = value1, column2 = value2.... WHERE [condition]; |
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”:
1 2 3 | UPDATE employees 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:
1 | SELECT * FROM employees WHERE employee_id = 2; |
This SQL statement should now return a table of results that looks like the following:
1 2 3 4 | employee_db=# SELECT * FROM employees WHERE employee_id = 2; employee_id | name | STATUS | salary -------------+-----------------+--------+-------- 2 | George Costanza | Salary | 80000 (1 ROW) |
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:
1 2 3 | UPDATE employees 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'
.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started