PostgreSQL Update Query

The PostgreSQL Update query is used to modify the existing rows of the column with the new value. If there is where clause mentioned then it will update only those records in which condition is qualifying. The columns that need to be modified should be put in the SET clause. To run the Update query, we should have the UPDATE privilege present on the table.

Following is the syntax of the PostgreSQL Update query.


Syntax:

UPDATE tablename SET column_1 = value_1, column_2 = value_2, column_3 = value_3...., column_n = value_n WHERE [condition];


1. Update Query With Where Clause


In this example, We will use the table PRODUCTS_DETAIL that we have created in create table section and use the Update command to update the column PRODUCT_NAME and set it to DARK CANDY where the PRODUCT_NUMBER is 101.


Command:

postgres=# SELECT * FROM products_detail;
postgres=# UPDATE products_detail SET product_name = 'DARK CANDY' WHERE product_number = 101;
postgres=# SELECT * FROM products_detail;


Output:

In the following output, we can see that the column data has been changed from CANDY to DARK CANDY.


postgresql update command


2. Update Query Without Where Clause

If there is a requirement to update all rows of one column or multiple columns then, we can use the Update query without mentioning the where clause.

In the below example. we will update all records of column PRODUCT_NAME and make it RICE.


Command:

postgres=# UPDATE products_detail SET product_name = 'Rice' ;


Output:

We can verify in the below output that the product_name column value has been set to Rice.


postgresql update command without where clause