Update query in PostgreSQL – Complete Guide

In this PostgreSQL tutorial, we will discuss the PostgreSQL UPDATE query to modify the rows of an existing table, and will also cover the following topics:

• PostgreSQL UPDATE query
• PostgreSQL UPDATE query with where clause syntax
• PostgreSQL UPDATE query with DEFAULT
• PostgreSQL UPDATE query with RETURNING statement
• PostgreSQL UPDATE query using column-list syntax
• PostgreSQL UPDATE query using the FROM clause syntax
• PostgreSQL UPDATE query with subquery
• PostgreSQL UPDATE query with join
• PostgreSQL UPDATE query with alias
• PostgreSQL UPDATE query with limit

PostgreSQL UPDATE query

PostgreSQL provides the feature of modifying the data of the pre-existing table by using the UPDATE query.

UPDATE query modifies the values of the specified columns in all the rows that follow the condition. Only the columns to be modified have to be specified in the UPDATE query.

The syntax of the UPDATE query is as follows:

UPDATE table_name
SET
column1 = value1,
column2 = value2,
column3 = value3,
...
...
...
columnN = valueN
WHERE condition;

In the above syntax,

• The table_name specifies the name of the table whose data has to be modified.
• The column1, column2, column3, …, columnN specifies the names of the columns of the table_name table, whose values has to be modified.
• The value1, value2, value3, …, valueN specifies the values to be assigned to the columns column1, column2, column3, …, columnN respectively of all the rows that satisfies the condition in WHERE clause.
• The condition after the WHERE clause is the expression that decides the rows to be updated by the UPDATE query.
• The UPDATE query when executes successfully, returns the command tag UPDATE count.
  • Here, the UPDATE is the keyword.
  • And, the count is the number of rows that gets updated by the UPDATE query, including the rows whose previous values are same as the updated one.

Let’s practice some examples to update rows in a table:

First Create a sample table in PostgreSQL as “house_details”:

CREATE TABLE house_details (
hno SERIAL PRIMARY KEY,
address VARCHAR(70),
area_sqmt INT,
sale_date DATE,
buyer VARCHAR(50));

\d

Populate some data into the table house_details (I have added the INSERT INTO command for reference):

INSERT INTO house_details (address, area_sqmt, sale_date, buyer)
VALUES 
('01743 Fulton Parkway', 12200, '4/7/2021', 'Rudiger Jurges'),
('57 Drewry Court', 14484, '8/26/2019', 'Paula Pimlott'),
('809 4th Drive', 5032, '10/6/2020', 'Marianne Stiddard'),
('06 La Follette Trail', 4608, '4/11/2021', 'Jud Ensten'),
('029 Farragut Circle', 6888, '7/17/2021', 'Lazar Beaton'),
('10528 Florence Trail', 12736, '6/27/2021', 'Tarah MacAfee'),
('3391 Sutteridge Park', 3128, '3/3/2021', 'Christal Clemerson'),
('7811 1st Parkway', 12368, '5/12/2021', 'Marie Kennaird'),
('22 Hanover Circle', 14618, '1/18/2020', 'Candace Mongain'),
('218 Crownhardt Hill', 11894, '4/14/2021', 'Susanetta Dumbleton'),
('80525 Mallory Hill', 19127, '11/17/2019', 'Odelinda Gelletly'),
('3111 Springs Center', 4377, '4/19/2020', 'Darelle Gath'),
('6 Scott Junction', 14927, '8/9/2019', 'Amberly Staggs'),
('6946 Judy Alley', 19727, '10/20/2020', 'Petronella Sitford'),
('0123 Little Fleur Terrace', 17338, '6/7/2021', 'Lucias Shaxby'),
('13771 Beilfuss Lane', 12979, '11/14/2019', 'Patricia Cranna'),
('7 Forest Lane', 19228, '9/18/2020', 'Clayton Dronsfield'),
('72 Starling Pass', 19116, '5/4/2021', 'Yurik Semered'),
('7628 Utah Parkway', 14446, '8/6/2019', 'Merline Daleman'),
('1 Northview Junction', 12053, '6/13/2021', 'Lenore Ladlow'),
('61523 Farwell Street', 11926, '10/22/2019', 'Gerald Clingoe'),
('8385 Hauk Crossing', 13337, '8/16/2020', 'Franni Proudler'),
('74 Russell Circle', 14698, '2/21/2020', 'Drugi Braniff'),
('81 Hoard Junction', 4944, '1/3/2020', 'Kimberlyn MacGillacolm'),
('3967 Grasskamp Court', 12418, '3/10/2020', 'Edwina Wardingley'),
('47713 Alpine Avenue', 18912, '10/29/2019', 'Prentice Hatt'),
('33302 Heffernan Center', 12336, '2/7/2020', 'Melanie Luca'),
('150 Lakeland Lane', 6400, '9/2/2019', 'Sarine Shuard'),
('083 Quincy Drive', 11340, '5/11/2020', 'Arline Winslet'),
('4 Vera Plaza', 17837, '8/29/2020', 'Haydon Strutton');

SELECT * FROM house_details limit(10);

SELECT count(*) FROM house_details;

Now, we have a sample table “house_details”, so let’s do the examples:

Read PostgreSQL TO_NUMBER() function

PostgreSQL UPDATE query with where clause syntax

We can add a WHERE clause in the UPDATE query to restrict the rows to be updated. If we use an UPDATE command without a WHERE clause, all the rows will get updated.

Let’s do an example to update rows that satisfy the condition specified in the WHERE clause.

SELECT * FROM house_details WHERE buyer = 'Clayton Dronsfield';

UPDATE house_details
SET address = '7 Forest Lane, Billington square'
WHERE buyer = 'Clayton Dronsfield';

SELECT * FROM house_details WHERE buyer = 'Clayton Dronsfield';

We have updated the address of the house, sold to the buyer ‘Clayton Dronsfield’ using the UPDATE query.

Read Postgresql date_trunc function

PostgreSQL UPDATE query with DEFAULT

We can modify the column(s) to the DEFAULT constraint value(s) of the respected columns, by specifying the value(s) as DEFAULT for the column(s) in the SET clause of the UPDATE query.

Let’s first add a DEFAULT value to the column area_sqmt of our sample table house_details.

And then practice updating the column with the DEFAULT value using the UPDATE query.

ALTER TABLE house_details
ALTER COLUMN area_sqmt SET DEFAULT 10000;

\d house_details

UPDATE house_details
SET address = '1 Northview Junction, Baston Fields', area_sqmt = DEFAULT
WHERE address = '1 Northview Junction' AND sale_date = '6/13/2021';

SELECT * FROM house_details
WHERE address = '1 Northview Junction, Baston Fields';

We have updated the value of the column area_sqmt with the DEFAULT value of the rows, satisfying the condition specified in the WHERE clause.

Read PostgreSQL TO_TIMESTAMP function

PostgreSQL UPDATE query with RETURNING statement

We can display the specified columns of the updated rows by adding RETURNING statement at the end of the UPDATE query.

We can also give the alias to the output returned by the UPDATE query. The syntax is as follows:

UPDATE table_name
SET
column1 = value1,
column2 = value2,
column3 = value3,
...
...
...
columnN = valueN
WHERE condition
RETURNING * | output_expression[ AS output_name];

In the above syntax,

• The RETURNING clause is added, followed by the column list to be displayed.
• We can either give an asterisk (*) to select all the columns, or we can give a column list as output_expression.
• The reference name output_name is optional.

Example :

SELECT * FROM house_details
WHERE buyer = 'Lenore Ladlow';

UPDATE house_details
SET area_sqmt = 12300, address = '74 Russell Circle, Castor Square'
WHERE buyer = 'Lenore Ladlow' AND address = '74 Russell Circle'
RETURNING address, area_sqmt, buyer AS updated_row;

We have updated the address column of the rows that follows the specified condition and added the RETURNING statement to display the specified columns of the updated rows.

Read Postgresql change column data type

PostgreSQL UPDATE query using column-list syntax

We can also update new values to the specified columns providing the values-list against the column-list in the UPDATE query.

Example :

SELECT * FROM house_details
WHERE buyer = 'Lenore Ladlow';

UPDATE house_details
SET (address, area_sqmt, sale_date) =
 ('06 La Follette Trail, New Cavill City', area_sqmt+1200, '2021-05-11')
WHERE buyer = 'Lenore Ladlow' AND address = '06 La Follette Trail';

SELECT * FROM house_details
WHERE buyer = 'Lenore Ladlow';

We have updated the values of address, area_sqmt, and sale_date columns by using the column-list syntax of the rows that follow the condition specified by the WHERE clause in the UPDATE query.

Read PostgreSQL TO_CHAR function – How to use

PostgreSQL UPDATE query using the FROM clause syntax

We can update the values of the columns of the rows of a table, based on some other table. We can use the other table by including it in the UPDATE query using the FROM clause.

To get a better grip on the concept, let’s practice an example:

SELECT * FROM buyers_detail;

SELECT * FROM house_details WHERE buyer in ('Lenore Ladlow', 'Prentice Hatt');

UPDATE house_details
SET area_sqmt = area_sqmt + 1000
FROM buyers_detail
WHERE house_details.buyer = buyers_detail.name 
AND buyers_detail.type = 'ult';

SELECT * FROM house_details WHERE buyer in ('Lenore Ladlow', 'Prentice Hatt');

We have updated the area_sqmt values of the rows for the buyers who are ‘ult’ (Ultra member) type by using the FROM clause syntax in the UPDATE query.

Read How to import CSV file into PostgreSQL

PostgreSQL UPDATE query with subquery

We can use the subqueries in an UPDATE query as per our need. We can write a subquery in the condition of the WHERE clause.

It is an alternative to do the same as done in the above topic, that is to update the values of the columns of the rows of a table, based on some other table.

The example to illustrate the concept is as follow:

SELECT * FROM buyers_detail;

SELECT * FROM house_details
WHERE buyer IN (SELECT name FROM buyers_detail WHERE type = 'pre');

UPDATE house_details
SET area_sqmt = area_sqmt + 700
WHERE buyer IN (SELECT name FROM buyers_detail WHERE type = 'pre')
RETURNING buyer, area_sqmt;

We have updated the area_sqmt values of the rows for the buyers who are ‘pre’ (Premium member) type by using a subquery in the condition of WHERE clause in the UPDATE query.

Read How to backup PostgreSQL database

PostgreSQL UPDATE query with join

We can use a join of the tables in the UPDATE query to update the values of the columns of the rows that follow a specified condition.

Let’s look at an example that illustrates the application of the join in an UPDATE query:

We have added a new column buyer_type with DEFAULT value ‘NA’ in the table house_details. We have then updated the table to reflect the changes.

Then, we have used the other table buyers_detail to update the values of the column buyer_type of house_details, based on the column type of the buyers_detail table.

ALTER TABLE house_details
ADD COLUMN buyer_type VARCHAR(10) DEFAULT 'NA';

UPDATE house_details
SET buyer_type = DEFAULT;

SELECT * FROM house_details LIMIT(10);

UPDATE house_details
SET buyer_type = type
FROM buyers_detail
WHERE buyers_detail.name = house_details.buyer
RETURNING hno, buyer, buyer_type;

We have updated the values of the column buyer_type of the table house_details based on the column type of the buyers_detail table using join.

Read PostgreSQL Export Table to CSV

PostgreSQL UPDATE query with alias

We can give the alias to the tables in the UPDATE query. The alias gives the ease of using the tables by short names and makes the code more readable.

Let’s take a look at the use of alias in the UPDATE query:

SELECT * FROM house_details LIMIT(5);

ALTER TABLE house_details
ADD COLUMN buyer_type VARCHAR(10) DEFAULT 'NA';

UPDATE house_details
SET buyer_type = DEFAULT;

SELECT * FROM house_details LIMIT(5);

UPDATE house_details h
SET buyer_type = type
FROM buyers_detail b
WHERE b.name = h.buyer
RETURNING hno, buyer, buyer_type;

We have done the same thing as done in the previous example but this time we have used the alias in place of the table name.

Read PostgreSQL drop all tables

PostgreSQL UPDATE query with limit

We can limit the number of rows to be updated that satisfies the condition specified in the WHERE clause by using the LIMIT keyword.

The LIMIT keyword followed by the number of rows to be considered has to be added at the end of the condition of the WHERE clause in the UPDATE query.

Let’s practice an example to get the concept more clear:

SELECT * FROM house_details WHERE area_sqmt < 9000;

UPDATE house_details
SET area_sqmt = area_sqmt + 500
WHERE area_sqmt IN
(SELECT area_sqmt
FROM house_details
WHERE area_sqmt < 9000
LIMIT 1);

SELECT * FROM house_details WHERE area_sqmt < 9000;

We have updated values of the columns of only one row out of the rows that satisfy the condition of the WHERE clause in the UPDATE query.

You may also like the following PostgreSQL tutorials:

• Postgres RegEx
• PostgreSQL DATE_PART() Function with examples
• PostgreSQL DROP TABLE

In this PostgreSQL tutorial, we have learned about the PostgreSQL UPDATE query to modify the rows of an existing table, and have also covered the following topics:

• PostgreSQL UPDATE query
• PostgreSQL UPDATE query with where clause syntax
• PostgreSQL UPDATE query with DEFAULT
• PostgreSQL UPDATE query with RETURNING statement
• PostgreSQL UPDATE query using column-list syntax
• PostgreSQL UPDATE query using the FROM clause syntax
• PostgreSQL UPDATE query with subquery
• PostgreSQL UPDATE query with join
• PostgreSQL UPDATE query with alias
• PostgreSQL UPDATE query with limit