PostgreSQL : UPDATE Statement

This PostgreSQL tutorial explains how to use the PostgreSQL UPDATE statement with syntax and examples.

Description

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

Syntax

The syntax for the UPDATE statement when updating one table in PostgreSQL is:

UPDATE table

SET column1 = expression1 | DEFAULT,

column2 = expression2 | DEFAULT,

...

[WHERE conditions];

Parameters or Arguments

column1, column2

The columns that you wish to update.

expression1, expression2

The new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

DEFAULT

The default value for that particular column in the table. If no default value has been set for the column, the column will be set to NULL.

WHERE conditions

Optional. The conditions that must be met for the update to execute. If no conditions are provided, then all records from the table will be updated.

Example - Update single column

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

UPDATE contacts

SET first_name = 'Jane'

WHERE contact_id = 35;

This PostgreSQL UPDATE example would update the first_name to 'Jane' in the contacts table where the contact_id is 35.

You could also use the DEFAULT keyword to set a column to its default value.

For example,

UPDATE contacts

SET first_name = DEFAULT

WHERE contact_id = 35;

This PostgreSQL UPDATE example would update the first_name to the default value for the field in the contacts table where the contact_id is 35. If no default value has been set for the first_name column in the contacts table, the first_name column will be set to NULL.

Example - Update multiple columns

Let's look at a PostgreSQL UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE contacts

SET city = 'Miami',

state = 'Florida'

WHERE contact_id >= 200;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This PostgreSQL UPDATE example would update the city to 'Miami' and the state to 'Florida' where the contact_id is greater than or equal to 200.