How to Update Multiple Columns in PostgreSQL

Last updated on July 25th, 2024 at 09:55 am

Often database programmers and web developers update single columns in PostgreSQL database. But sometimes you may need to change multiple column values in PostgreSQL. You can modify multiple column values using a single UPDATE statement. It allows you to bulk update multiple columns at one go. In this article, we will look at how to update multiple columns in PostgreSQL.

How to Update Multiple Columns in PostgreSQL

It is very easy to update multiple columns in PostgreSQL. Here is the syntax to update multiple columns in PostgreSQL.

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

In the above SQL statement, you need to specify table name, and mention column names and their new values as a comma-separated list, and optionally mention a WHERE condition to update only certain rows in your table.

It returns the number of rows updated as shown below where count is the number of rows updated.

UPDATE count

Also read : How to Compare Arrays in PostgreSQL

For example, let us say you have the following table sales (id, order_date, amount).

postgres=# create table sales(id int, 
           order_date date, 
           amount int);

Let us insert some data in this newly created table, using INSERT statement.

postgres=# insert into sales(id, order_date, amount) 
           values(1, '2020-12-01', 100),
                 (2,'2020-12-02',250),
                 (3,'2020-12-03',300);

Also read : How to Change User Password in PostgreSQL

We will verify if the rows have been properly added using SELECT statement.

postgres=# select * from sales;
id | order_date | amount
----+------------+--------
1 | 2023-12-01 | 100
2 | 2023-12-02 | 250
3 | 2023-12-03 | 300

Here is the SQL query to update both order_date and amount columns in sales table where id=3.

postgres=# update sales 
           set order_date='2020-12-04', 
               amount=250 where id=3;
UPDATE 1

The above query returns ‘UPDATE 1’ indicating that 1 row has been updated. We will run a select query to check the data. We have highlighted the updated row in bold.

postgres=# select * from sales;
id | order_date | amount
----+------------+--------
1 | 2020-12-01 | 100
2 | 2020-12-02 | 250
3 | 2020-12-04 | 250

As you can see it is very easy to update multiple columns in PostgreSQL.

Please note, if the data type of column is different from that of its new value, then PostgreSQL will try to convert it into the column’s data type before updating. For example, if you try to set a numerical value to a varchar column, PostgreSQL will convert it into a string before updating the column. If that is not possible, then it will give an error. At that point, all updates will fail. So please be careful when assigning new values to a column, especially if you are updating multiple columns at one go.

Alternatively, you can also type cast your values to the correct data type in UPDATE statement itself. Here is an example, where we cast a string ‘character ‘250’ to integer 250 using cast() function in UPDATE statement.

postgres=# update sales 
           set order_date='2020-12-04', 
               amount=cast('250' as integer) where id=3;
UPDATE 1

You can also use ‘::’ operator to cast the data into a different data type.

postgres=# update sales 
           set order_date='2020-12-04', 
               amount='250'::integer where id=3;
UPDATE 1

Conclusion

In this article, we have learnt how to update multiple columns at one go using UPDATE statement in PostgreSQL. Basically, you need to mention all columns, along with their values, in a comma-separated manner. Also, you need to be careful that each value needs to match the data type of its column to avoid data mismatch error. In such cases, use cast() function or ‘::’ operator to cast your value to appropriate data type.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!