PostgreSQL Tutorial: UPDATE

Summary: in this tutorial, you will learn how to use the PostgreSQL UPDATE statement to update existing data in a table.

Table of Contents

Introduction to the PostgreSQL UPDATE statement

The PostgreSQL UPDATE statement allows you to modify data in a table. The following illustrates the syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

In this syntax:

• First, specify the name of the table that you want to update data after the UPDATE keyword.
• Second, specify columns and their new values after SET keyword. The columns that do not appear in the SET clause retain their original values.
• Third, determine which rows to update in the condition of the WHERE clause.

The WHERE clause is optional. If you omit the WHERE clause, the UPDATE statement will update all rows in the table.

When the UPDATE statement is executed successfully, it returns the following command tag:

UPDATE count

The count is the number of rows updated including rows whose values did not change.

Returning updated rows

The UPDATE statement has an optional RETURNING clause that returns the updated rows:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition
RETURNING * | output_expression AS output_name;

PostgreSQL UPDATE examples

Let’s take some examples of using the PostgreSQL UPDATE statement.

Setting up a sample table

The following statements create a table called courses and insert some data into it:

DROP TABLE IF EXISTS courses;

CREATE TABLE courses(
	course_id serial primary key,
	course_name VARCHAR(255) NOT NULL,
	description VARCHAR(500),
	published_date date
);

INSERT INTO 
	courses(course_name, description, published_date)
VALUES
	('PostgreSQL for Developers','A complete PostgreSQL for Developers','2020-07-13'),
	('PostgreSQL Admininstration','A PostgreSQL Guide for DBA',NULL),
	('PostgreSQL High Performance',NULL,NULL),
	('PostgreSQL Bootcamp','Learn PostgreSQL via Bootcamp','2013-07-11'),
	('Mastering PostgreSQL','Mastering PostgreSQL in 21 Days','2012-06-30');

The following statement returns the data from the courses table:

SELECT * FROM courses;

1) PostgreSQL UPDATE – updating one row

The following statement uses the UPDATE statement to update the course with id 3. It changes the published_date from NULL to '2020-08-01'.

UPDATE courses
SET published_date = '2020-08-01' 
WHERE course_id = 3;

The statement returns the following message indicating that one row has been updated:

UPDATE 1

The following statement selects the course with id 3 to verify the update:

SELECT * FROM courses
  WHERE course_id = 3;

2) PostgreSQL UPDATE – updating a row and returning the updated row

The following statement updates course id 2. It modifies published_date of the course to 2020-07-01 and returns the updated course.

UPDATE courses
SET published_date = '2020-07-01'
WHERE course_id = 2
RETURNING *;

Summary

• Use the PostgreSQL UPDATE statement to update data in one or more columns of a table.
• Use the RETURNING clause to return the updated rows from the UPDATE statement