Previous
Next

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.

Syntax: Update 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.

Example: Update a Row
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.

Example: Update All 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:

Example: RETURNING *
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:

Example: RETURNING 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:

Previous
Next