Movatterモバイル変換

[0]ホーム
URL:

You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial

PostgreSQL UPDATE

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

Introduction to the PostgreSQL UPDATE statement

The PostgreSQLUPDATE statement allows you to update data in one or more columns of one or more rows in a table.

Here’s the basic syntax of theUPDATE statement:

UPDATE table_nameSET column1 = value1,    column2 = value2,    ...WHERE condition;

In this syntax:

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

TheWHERE clause is optional. If you omit theWHERE clause, theUPDATE statement will update all rows in the table.

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

UPDATE count

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

Returning updated rows

TheUPDATE statement has an optionalRETURNING clause that returns the updated rows:

UPDATE table_nameSET column1 = value1,    column2 = value2,    ...WHERE conditionRETURNING * | output_expression AS output_name;

PostgreSQL UPDATE examples

Let’s take some examples of using the PostgreSQLUPDATE statement.

Setting up a sample table

The following statementscreate a table calledcourses andinsert data into it:

CREATE TABLE courses(  course_idserial PRIMARY KEY,  course_nameVARCHAR(255)NOT NULL,  priceDECIMAL(10,2)NOT NULL,  description VARCHAR(500),  published_datedate);INSERT INTO courses( course_name, price,description, published_date)VALUES('PostgreSQL for Developers',299.99,'A complete PostgreSQL for Developers','2020-07-13'),('PostgreSQL Admininstration',349.99,'A PostgreSQL Guide for DBA',NULL),('PostgreSQL High Performance',549.99,NULL,NULL),('PostgreSQL Bootcamp',777.99,'Learn PostgreSQL via Bootcamp','2013-07-11'),('Mastering PostgreSQL',999.98,'Mastering PostgreSQL in 21 Days','2012-06-30');SELECT * FROM courses;

Output:

course_id |         course_name         | price  |             description              | published_date-----------+-----------------------------+--------+--------------------------------------+----------------         1 | PostgreSQL for Developers   | 299.99 | A complete PostgreSQL for Developers | 2020-07-13         2 | PostgreSQL Admininstration  | 349.99 | A PostgreSQL Guide for DBA           | null         3 | PostgreSQL High Performance | 549.99 | null                                 | null         4 | PostgreSQL Bootcamp         | 777.99 | Learn PostgreSQL via Bootcamp        | 2013-07-11         5 | Mastering PostgreSQL        | 999.98 | Mastering PostgreSQL in 21 Days      | 2012-06-30(5 rows)

1) Basic PostgreSQL UPDATE example

The following statement uses theUPDATE statement to update the course with id 3 by changing thepublished_date to'2020-08-01'.

UPDATE coursesSET 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 retrieves the course with id 3 to verify the update:

SELECT course_id, course_name, published_dateFROM coursesWHERE course_id = 3;

Output:

course_id |         course_name         | published_date-----------+-----------------------------+----------------         3 | PostgreSQL High Performance | 2020-08-01(1 row)

2) Updating a row and returning the updated row

The following statement uses theUPDATE statement updatepublished_date of the course id 2 to2020-07-01 and returns the updated course.

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

Output:

course_id |        course_name         | price  |        description         | published_date-----------+----------------------------+--------+----------------------------+----------------         2 | PostgreSQL Admininstration | 349.99 | A PostgreSQL Guide for DBA | 2020-07-01(1 row)

3) Updating a column with an expression

The following statement uses anUPDATE statement to increase the price of all the courses 5%:

UPDATE coursesSET price = price * 1.05;

Because we don’t use a WHERE clause, the UPDATE statement updates all the rows in thecourses table.

Output:

UPDATE 5

The following statement retrieves data from thecourses table to verify the update:

SELECT * FROM courses;

Output:

SELECT  course_name,  priceFROM  courses;

Output:

course_name         |  price-----------------------------+--------- PostgreSQL for Developers   |  314.99 PostgreSQL Bootcamp         |  816.89 Mastering PostgreSQL        | 1049.98 PostgreSQL High Performance |  577.49 PostgreSQL Admininstration  |  367.49(5 rows)

Summary

  • Use theUPDATE statement to update data in one or more columns of a table.
  • Specify a condition in a WHERE clause to determine which rows to update data.
  • Use theRETURNING clause to return the updated rows from theUPDATE statement

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp