PostgreSQL UPDATE guide

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.

Basic Syntax

The most basic form of the UPDATE statement is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here:

• table_name is the name of the table you wish to update.
• SET specifies the column names and their new values.
• WHERE defines which rows to update. If omitted, all rows will be updated!

Example:

To update a specific user’s email address in a users table:

UPDATE users
SET email = '[email protected]'
WHERE username = 'john_doe';

Conditional Updates

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!

Example:

To update a price for a specific product in a products table:

UPDATE products
SET price = 19.99
WHERE product_id = 1002;

Updating Multiple Columns

You can update multiple columns in a single UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

To update both the email and phone number for a user:

UPDATE users
SET email = '[email protected]', phone = '123-456-7890'
WHERE username = 'john_doe';

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:

UPDATE table_name
SET column1 = (SELECT ...)
WHERE condition;

Example:

Assuming we have two tables, orders and products, and we want to update the orders table with the latest product price:

UPDATE orders
SET order_price = (SELECT price FROM products WHERE products.product_id = orders.product_id)
WHERE order_id = 12345;

RETURNING Clause

PostgreSQL provides a powerful RETURNING clause that returns the rows affected by the UPDATE:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
RETURNING column1, column2, ...;

Example:

Updating a user’s email and returning the user’s id and the new email:

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

Performance Considerations

1. Indexes: Make sure columns in the WHERE clause are indexed to speed up updates, especially for large tables.
2. Bulk Updates: For large updates, consider batching them to reduce lock contention.

Best Practices

Basedash is built as an AI-native BI platform, so teams can go from ad hoc SQL to trusted answers and dashboards quickly, without the overhead of traditional BI setup.

Hopefully this guide has provided a clear understanding of the UPDATE statement in PostgreSQL. Happy querying!