PostgreSQL: Update Data in a Table
Here you will learn how to update data in the table in PostgreSQL database.
In PostgreSQL, use the UPDATE statement to modify existing data in the table. The UPDATE statement only updates data in the table and does not modify the structure of a table.
UPDATE <table_name>
SET <column1> = <value1>,
<column2> = <value2>,
...
WHERE <condition<
RETURNING * | <output_expression> AS <output_name>;
In above syntax:
- Specify the name of the table you want to update data after the UPDATE keyword.
- After the SET keyword, specify one or more combinations of column and their new values. The columns which are not specified in the SET clause will have their original values.
- The WHERE clause is optional which limits the update operation specific to the specified condition. If you do not specify the WHERE clause, Postgres will update all the rows of a table.
- The RETURNING clause is optional which will return a list of all updated rows or values of the specified column.
Let's update data in the following employee
table.

Updating a Single Row
Use the WHERE clause with the UPDATE statement and specify a primary key value to update a single row in the table.
For example, the following UPDATE statement will update an email of an employee
whose emp_id=1
.
UPDATE employee
SET email = '[email protected]'
WHERE emp_id = 1;
In the above example, the UPDATE employee
indicates that we want to update data in the employee
table, SET email = '[email protected]'
specifies that the email column should be updated to '[email protected]'
.
The WHERE emp_id = 1
will update a row where emp_id
value is 1, thereby it limits the update operation to only one row.
The following display the result of the above query in pgAdmin:

The UPDATE statement returns UPDATE 1
which indicates that it has updated 1 row.
Let's verify updated data using the SELECT query shown below.

WARNING: If you don't specify the WHERE
clause, then it will update the email column value in all the rows.
UPDATE employee
SET email = '[email protected]';
RETURNING Clause with UPDATE Statement
The RETURNING clause returns the updated rows or column values. If you specify RETURNING *
then it will return all the updated rows and if you specify RETURNING column_name
then it will return values of the specified columns. You can specify multiple columns separated with a comma.
The following UPDATE statement returns all the updated rows:
UPDATE employee
SET birthdate = NULL,
salary = 10000,
last_name = 'Blamire'
WHERE emp_id = 4
RETURNING *;
As you can see, birthdate
is set to NULL, salary
is set to 10000, and the last_name
is changed to "Blamire"
where emp_id = 4
.
The order of the columns is not important with the SET clause. The following displays the result of the above query in pgAdmin:

The RETURNING clause can used with one or more columns. The following UPDATE statement returns multiple columns:
UPDATE employee
SET last_name = 'Blamire',
birthdate = NULL,
salary = 10000
WHERE emp_id = 4
RETURNING first_name, last_name, salary;
The RETURNING clause can use any columns of the table, not necessarily the column used in the SET clause e.g. the first_name
column was not updated but we can still take it with the RETURNING clause.
The above query will display the following result in pgAdmin:
