PostgreSQL UPDATE: Updating Data in a Table

Summary: in this tutorial, you will learn how to use the PostgreSQL UPDATE statement to update data in one or more rows in a table.

Introduction to PostgreSQL UPDATE statement

To update data in a table, you use the PostgreSQL UPDATE statement.

Here’s the syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the table you want to update the data after the UPDATE keyword.
Second, provide the column names and new values in the SET clause.
Third, specify a condition in the WHERE clause to determine which rows to update. The UPDATE statement will only update the rows that satisfy the condition.

The WHERE clause is optional. If you omit the WHERE clause, the UPDATE statement will update the columns of all rows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Forgetting the WHERE clause could lead to the unintended changes because the UPDATE statement will update every rows in the table.

To avoid this risk, you should always include a WHERE clause that precisely target the rows you intend to update.

The UPDATE statement returns the number of rows updated to the client.

Setting up a sample table

We’ll update data in the inventories table:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);


INSERT INTO inventories (name, brand, quantity, price)
VALUES ('iPhone 14 Pro', 'Apple', 10, 999.99),
       ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
       ('Pixel 7 Pro', 'Google', 8, 899.99),
       ('Xperia 1 IV', 'Sony', 7, 1299.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Updating one row in a table example

The following example uses the UPDATE statement to change the quantity of the product with the name "iPhone 14 Pro" to 30:

UPDATE inventories
SET
  quantity = 30
WHERE
  name = 'iPhone 14 Pro';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

UPDATE 1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the UPDATE statement updated one row successfully. To verify the update, you can retrieve data from the inventories table:

SELECT
  name,
  quantity
FROM
  inventories
WHERE
  name = 'iPhone 14 Pro';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     name      | quantity
---------------+----------
 iPhone 14 Pro |       30Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Updating all rows in a table

The following example uses the UPDATE statement to reduce the price of all products by 10% by multiplying the price with 0.9 (90%):

UPDATE inventories
SET
  price = price * 0.9;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

UPDATE 4Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

We don’t use the WHERE clause in this example, so the statement updates all the rows. The output shows that the UPDATE statement updated four rows in the inventories table.

The following SELECT statement retrieves all rows from the inventories table:

SELECT
  name,
  price
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       name       |  price
------------------+---------
 Galaxy S23 Ultra | 1079.99
 Pixel 7 Pro      |  809.99
 Xperia 1 IV      | 1169.99
 iPhone 14 Pro    |  899.99Code language: plaintext (plaintext)

The output shows that the statement updated the prices successfully.

Returning the updated rows

The UPDATE statement offers the RETURNING clause that returns the updated rows.

UPDATE table_name
SET
  column1 = value1,
  column2 = value2
WHERE
  condition 
RETURNING 
   column1, column2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The RETURNING clause returns the updated rows containing the columns specified in the clause.

If you want to return all the columns of the updated row, you can use the asterisk (*) shorthand in the RETURNING clause.

This is a convenient way to return all the columns without having to list them individually.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example uses the UPDATE statement to update the price of the Pixel 7 Pro product to 750 and return the updated row:

UPDATE inventories
SET
  price = 750
WHERE 
  name = 'iPhone 14 Pro'
RETURNING *;Code language: plaintext (plaintext)

Output:

     name      | brand | quantity | price
---------------+-------+----------+--------
 iPhone 14 Pro | Apple |       30 | 750.00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

Use the UPDATE statement to update data in a table.
Omit the WHERE clause will update all rows in a table.
Use the RETURNING clause to return updated rows.