In PostgreSQL, the UPDATE query is used with the assistance of the SET clause to update/modify the table’s record. In PostgreSQL, different clauses like WHERE, RETURNING, etc., can be used with the UPDATE query to achieve different functionalities. The WHERE clause is used with the UPDATE command to modify the record of a specific row. In the WHERE clause, you can specify several conditions with the aid of AND and OR operators. In PostgreSQL, omitting the WHERE clause will update the whole table.

This post is going to explain the working of the Postgres UPDATE query using some examples. So, let’s begin!

How to Use UPDATE Query in PostgreSQL?

Let’s consider the following syntax to get a basic understanding of the UPDATE query in PostgreSQL.

UPDATE tab_name  
SET col_1 = val_1,  
col_2 = val_2,
...  
col_N = val_N
WHERE  
condition;

Consider the below-listed points to analyze the working of the UPDATE query:

● Write the UPDATE keyword followed by the table name to update the table’s record. In the above snippet, tab_name represents a table to be updated.

● The SET clause takes comma-separated column-value pairs. Specify the column names and their updated values.

● In the above-snippet, col_1, col_2, …, col_N are the columns to be updated, while val_1, val_2, …, val_N, are their respective values.

● WHERE clause takes a condition on the basis of which the selected table will be updated.

Example: How to Update a single row in PostgreSQL?

We have an existing table named “team_members”. We will utilize the UPDATE query to update a specific row of the “team_members” table.

Step 1: Get the Table’s Details Using SELECT Query

Let’s run the SELECT query to see all the data of the selected table:

SELECT * FROM team_members;

Step 2: Update the Selected Row Using UPDATE Query

Suppose we want to update the player name from “Paul” to “Marsh”. To do this, execute the below-given command:

UPDATE team_members  
SET player_name = 'Marsh'
WHERE  
player_id = 5;

Let’s analyze the working of the above-given query step-by-step:

- team_members is the table to be updated.

- Marsh is the value to be updated in the player_name column.

- The updated value will be assigned to the player whose player_id = 5.

The error-free output verifies that the UPDATE query gets executed successfully.

Step 3: Verify the Updated Row Using the Select Command

Let’s run the below-given command to verify whether the selected row has been updated or not:

SELECT * FROM team_members;

The above snippet verified that the targeted row had been updated successfully.

RETURNING Clause in Postgres UPDATE Query

In PostgreSQL, the UPDATE query can accept an optional clause named RETURNING that is used to return the updated rows. The syntax of the UPDATE query with the RETURNING clause will go like this:

UPDATE tab_name
SET col_1 = val_1,
  col_2 = val_2,
  ...
WHERE condition
RETURNING *;

RETURNING * clause will return the updated rows.

Example: How to Use the RETURNING Clause With UPDATE Query in PostgreSQL?

Suppose we want to update and return the player_name whose id is 8. To do this, let’s execute the UPDATE query along with RETURNING clause:

UPDATE team_members  
SET player_name = 'Joe'
WHERE player_id = 8
RETURNING *;

The output clarifies that the RETURNING clause succeeded in returning the updated row.

Conclusion

PostgreSQL allows us to update the record of any existing table using the UPDATE query. In PostgreSQL, different clauses like WHERE, RETURNING, etc., can be used with the UPDATE query to serve different purposes. WHERE clause takes a condition based on which the selected table will be updated. While the RETURNING clause returns the updated record. This post explained several use cases of the UPDATE query with the help of examples.