How to update rows in PostgreSQL

Updating rows in a PostgreSQL database is performed using the UPDATE statement, which allows you to modify existing data in a table. The basic syntax is UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition;. The SET clause specifies the columns to be updated and the new values they should receive. Similar to the DELETE statement, the WHERE clause is essential as it determines which rows will be affected by the update. If the WHERE clause is omitted, all rows in the table will be updated, which may not be the intended outcome.

Complex conditions can be specified in the WHERE clause to target specific rows for updating. You can update rows based on comparisons, range conditions, or the results of subqueries. For example, UPDATE products SET price = price * 1.1 WHERE category = 'Electronics'; would increase the price by 10% for all products in the 'Electronics' category.

PostgreSQL also supports the RETURNING clause in the UPDATE statement, enabling you to retrieve information about the updated rows immediately. This feature can be very helpful for verifying the updates or for subsequent operations that depend on the updated data. An example usage is UPDATE employees SET salary = salary + 500 WHERE department = 'Sales' RETURNING *;, which would give all employees in the Sales department a raise of 500 and return the updated rows. This capability ensures that data manipulation is both transparent and verifiable.