PostgreSQL UPDATE statement.

Preamble

The PostgreSQL UPDATE statement is used to update existing table entries in a PostgreSQL database.

The syntax for the UPDATE statement when updating a single table in PostgreSQL

UPDATE table
SET column1 = expression1_id | DEFAULT,
column2 = expression2_id | DEFAULT,
…
[WHERE conds];

Parameters and arguments of the statement

• column1, column2 – Columns that you want to update.
• expression1_id, column2_id – New values for assigning column1, column2. Therefore, column1 will be assigned the value expression1, column2 will be assigned the value2, etc.
• DEFAULT – The default value for this particular column in the table. If the default value for a column is not set, the column will be set to NULL.
• WHERE conds – Optional. The conditions that must be met to perform the update. If no conditions are set, all entries in the table will be updated.

Example of how to update a single column

Let’s look at a very simple example of a PostgreSQL UPDATE query.

UPDATE contacts
SET first_name = 'Helen'
WHERE contact_id = 35;

In this example, the value of first_name will be updated to ‘Helen’ in the contacts table, where contact_id is 35.
You can also use the keyword DEFAULT to set the default value for the column.
For example,

UPDATE contacts
SET first_name = DEFAULT
WHERE contact_id = 35;

In this example, the first_name will be updated to the default value for the field in the contacts table, where contact_id is 35. If the default value is not present in the contacts table, the first_name column will be set to NULL.

Example how to update several columns

Consider the UPDATE example for PostgreSQL, where you can update several columns with one UPDATE statement.

UPDATE contacts
SET city = 'Abilene',
state = 'Beaumont'
WHERE contact_id >= 200;

If you want to update multiple columns, you can do so by separating the column/value pairs with commas.
In this PostgreSQL example of UPDATE, the value of the city will be changed to ‘Abilene’ and the state will be changed to ‘Beaumont’ where contact_id is greater than or equal to 200.

PostgreSQL: How to Update Records | Course

 

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.