Postgres update multiple columns

Postgresql provides a facility for the user to update the already present data by using an update command. This command is used to update single and multiple columns in a single command. This article will contain some examples to elaborate on the working of an UPDATE statement for single or multiple columns.


UPDATE name_of_table
SET columnname1= value1, columnname2= value2
WHERE condition;

Description of syntax

In the query, first, we use the name of the table we want to alter. Then in the set command, the column names are written that are to be changed. These changes are followed by the condition. And these conditions are written in the where clause. The where clause is an optional clause because if you remove this clause, the changes will be applied to the whole table, including all rows and columns. Hence, to change the specified columns and rows, we use this where clause in the UPDATE statement.

Implementation of the Update command

To implement the commands to update columns and more than one column, we need to have a relation in the PostgreSQL database, to which we need to alter the data. So we will create a table named syllabus that will contain all the syllabus information regarding id, name, and description of the syllabus with the date. All the data types of the columns are taken accordingly.

>> CREATE TABLE syllabus (
id serial PRIMARY KEY,
description VARCHAR(500),
published_date DATE );

After the table is created, we will insert data in it by using an INSERT statement.

>> INSERT INTO syllabus (name, description, published_date) VALUES ('PostgreSQL for newcommers','A complete PostgreSQL for users', '2020-07-13'), ('PostgreSQL relations','A PostgreSQL Guide for tables', NULL), ('PostgreSQL High Performance', NULL, NULL), ('PostgreSQL indexes','Learn PostgreSQL using indexes', '2013-07-11'), ('All in one guide-line', 'Mastering PostgreSQL in 21 Days', '2012-06-30');

Each value is entered with the quotes. Using the commas can cause an error. We have not entered the ids of the table, as PostgreSQL generates the numeric values automatically according to the number of rows.

>> SELECT * FROM syllabus;

Every 5 rows of data contain a description of 4 syllabuses with the published date of 3 syllabuses. So we will fill these empty spaces in the columns of the relation. First, we will see a single column update. In the below command, we will add the published date to the row of id 3. So we will use an UPDATE command.

>> UPDATE syllabus SET published_date = '2020-08-01' WHERE id =3;

You will see that the notification is displayed to show that the query has updated one row. You can check the change in the table by using a select statement. The update statement contains two columns: one where you need to create change, and the second column is used to make references. In the above example, the id column is used to locate the specified row, whereas in all 4 rows, a particular column. i.e. Published_date is accessed, and the value is added at that place.

>>UPDATE syllabus SET published_date= '2020-07-01' WHERE id =2 Returning *;

This query will update the table syllabus and set the date column with a new date to fill the space in the column. The ‘returning *’ is used to return the whole record of the row having that column that is updated. So in this way, a single column is updated and displays the changes we made.

Now we will use the update command to create a change in the relationship in more than a single column. To elaborate on this concept, we will create another table named sample to contain the information of people living in different cities.

>> CREATE TABLE sample(

Similarly, insert rows in the relation sample through an insert statement.

>> INSERT INTO sample VALUES  ('john johny', 22, 'New York'), ('Raya david',23, 'NEW DELHI'),  ('Ananya koreat', 22, 'Istanbul'),  ('Jackob Ratiya', 30,'Mumbai'), ('Rose witson', 26,'Baghdad'),  ('Mark Adverd', 31 ,'Istanbul'), ('Kolson mackind', 34, 'Sharja'), ('SHEETAL Ahuja', 32, 'Istanbul');

After that, view the data by using the select statement.

>> SELECT * FROM sample;

In the previous example, we have entered some limited data in the rows and columns to fill the space later with the help of the UPDATE command to alter the table. The columns that don’t have data were filled using the command. But now need to exchange or replace the already existing data in the sample relation. In this case, two columns alteration is involved. These two columns get altered by providing a reference to a third column. This column is mostly the id of the relation. But it is not compulsory. Anyways, moving towards the example, we have used here.

>> UPDATE sample SET age=30, city='Washington D.C' WHERE city= 'Istanbul';

The query will work in such a way that first the column city will be accessed, and then it will be checked that the rows having the city name is Istanbul, the city is replaced with the ‘Washington D.C’. And the same row having the column age is also altered with 30.

>> SELECT * FROM sample;

You can see that all three rows have been changed, and all of them are written collectively at the end of the relation. Both the column’s age and city are updated.

Update columns via pgAdmin panel

When you open the admin panel, on providing the user password, the connection of the PostgreSQL database is established, and then you can alter the tables and schema or any other object. There are two ways in pgAdmin to use an UPDATE command. One is that we directly write commands as we do in the psql shell.

Here we will use the update command to apply the query on the table syllabus.

>> UPDATE syllabus SET published_date = '2020-07-01' WHERE id =3 RETURNING *;

This will update a single column where the id is 3, and all the records will be returned and displayed by using the same command.

The second method is to expand the database, and then the schema, lead to the tables, navigate towards the table respectively, right-click on the sample table; a drop-down bar will be opened. A further drop-down will be seen in which all the possible query names are written, as we need to update to select the update script.

Now you will see that on selecting that option, a query editor is automatically filled with an update command. But you only need to identify the columns you want to use.

We will edit this command; the name and description are removed; only we will use the id and the date column.


This article refers to the use of an update command. An update query is used both via psql and through the pgAdmin panel by using the altered scripts. We have used examples for alteration in single as well as multiple columns. A single thing is kept in mind while using an update statement to specify a column where a clause must be applied.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.