PostgreSQL UPDATE guide
October 23, 2023
PostgreSQL is a powerful, open-source relational database system. One of the essential operations any developer or DBA will perform is updating existing data. This guide walks you through the
UPDATE statement in PostgreSQL, its syntax, options, and some best practices.
The most basic form of the
UPDATE statement is:
table_nameis the name of the table you wish to update.
SETspecifies the column names and their new values.
WHEREdefines which rows to update. If omitted, all rows will be updated!
To update a specific user's email address in a
It's crucial to use the
WHERE clause to specify which rows you intend to update. Without it, you'll update every row in the table!
To update a price for a specific product in a
Updating Multiple Columns
You can update multiple columns in a single
To update both the email and phone number for a user:
Using Subqueries in UPDATE
You can use a subquery with
UPDATE to set column values based on data from another table or a more complex query:
Assuming we have two tables,
products, and we want to update the
orders table with the latest product price:
PostgreSQL provides a powerful
RETURNING clause that returns the rows affected by the
Updating a user's email and returning the user's id and the new email:
- Indexes: Make sure columns in the
WHEREclause are indexed to speed up updates, especially for large tables.
- Bulk Updates: For large updates, consider batching them to reduce lock contention.
- Always use the WHERE clause: Omitting the
WHEREclause will update all rows, which is often not what you intend.
- Test before applying: Always test your
UPDATEstatements on a backup or staging environment first.
- Transaction use: Use transactions when you need to ensure multiple updates occur together without interruption.
- Backup regularly: Ensure you have regular backups and verify their integrity. Mistaken updates can then be rolled back.
- Use a UI if possible: In most cases, it’s safer and easier to use a graphical UI for updating records in PostgreSQL. Consider using a tool like Basedash to generate an admin panel on top of your PostgreSQL database, allowing you to perform CRUD operations without needing to know SQL.
Hopefully this guide has provided a clear understanding of the
UPDATE statement in PostgreSQL. Happy querying!
How to enable row-level security (RLS) in PostgreSQL
How to list users in PostgreSQL
How to change the default port in PostgreSQL