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.