PostgreSQL: How to Update a Row and Return Updated Record

Introduction

Updating a record and immediately retrieving the modified data is a common task in database operations. In this tutorial, we’ll learn how to update a row in PostgreSQL and return the updated record using practical examples.

The Basics of UPDATE

The UPDATE statement in PostgreSQL is used to change the values of specified columns in one or more rows. Alongside the basic syntax, PostgreSQL offers a RETURNING clause that can return columns of the updated row.

UPDATE table_name SET column_name = value WHERE condition RETURNING *;

Using the RETURNING Clause

After an UPDATE operation, it’s often necessary to work with the updated data. This is where the RETURNING clause becomes useful.

UPDATE users SET email = '[email protected]' WHERE user_id = 1 RETURNING user_id, email;

Advanced Usage

PostgreSQL allows more complex operations involving joins, subqueries, and more, even while updating and returning data. Let’s explore updates with a subquery and a join.

Updating with a Subquery

A subquery can provide a dynamic value for the update based on other data in the database.

UPDATE users SET last_login = now() WHERE user_id = (SELECT user_id FROM logins WHERE logins.session_id = 'xyz') RETURNING user_id, last_login;

Updating with a Join

In cases involving related data, updating rows via a join can be performed. Here’s an example where users are updated based on their roles.

UPDATE users SET users.active = FALSE FROM user_roles WHERE users.user_id = user_roles.user_id AND user_roles.role_name = 'inactive' RETURNING users.*;

Updating and Ordering

We might want to update specific rows and return the results in a certain order:

UPDATE users SET last_seen = now() WHERE active = TRUE RETURNING users.* ORDER BY last_seen DESC LIMIT 10;

Handling NULLs with COALESCE

Use the COALESCE function to handle potential NULL values:

UPDATE users SET phone = COALESCE(phone, 'N/A') WHERE user_id = 1 RETURNING user_id, phone;

Update and Get Affected Row Count

To return the count of affected rows without the records themselves, use a query language extension or a scripting language.

// An example with PL/pgSQL.
DO $
DECLARE
  rows_affected integer;
BEGIN
  UPDATE users SET last_login = now() WHERE active = TRUE RETURNING user_id INTO rows_affected;
  RAISE NOTICE '% rows updated.', rows_affected;
END
$;

Examples in A Client Language

Here are examples of how to handle the returned records in application code using Python with the psycopg2 library.

import psycopg2

conn = psycopg2.connect('your_connection_string')
cursor = conn.cursor()
cursor.execute("UPDATE users SET name = 'John Doe' WHERE user_id = 2 RETURNING *")
updated_user = cursor.fetchone()
print(updated_user)

Error Handling and Transaction Management

It’s essential to manage errors and transactions properly to maintain data integrity. When performing updates in a client language, utilize try/except blocks and transaction controls offered by the driver or language of choice.

Performance Considerations

Updating records and immediately returning them can be expensive in terms of performance. Efficient indexing, reasonable condition specifications, and well-thought-out schemas can mitigate performance penalties.

Conclusion

Mastering the UPDATE statement with the RETURNING clause in PostgreSQL allows for streamlined and efficient workflows. By following the examples provided, you now know how to update a row and retrieve the updated data instantly. Utilize this powerful feature to enhance database operations in your applications.