intro
Let’s take a look at the essentials of using the Postgres UPDATE statement to update data in one or more columns of one or more rows in a table.
In this technical article, we will look into the Postgres UPDATE statement, and understand its statement syntax. From updating single and multiple rows to incorporating conditions, and expressions, we will learn the ins and outs behind data modification in PostgreSQL.
Time to get started!
How to Use the UPDATE Postgres Statement
Here’s the basic syntax of the Postgres UPDATE statement:
1
UPDATE table_name
2
SET column1 = value1,
3
column2 = value2,
4
...
5
WHERE condition;
Let’s break the building blocks of this syntax:
Before we go into understanding the logic behind the implementation of the UPDATE statement in Postgres, it is important for us to note that:
Postgres UPDATE: Real-World Examples
Time to explore some examples of Postgres UPDATE. Set up a demo table by running the query below:
1
CREATE TABLE dbs (
2
db_id SERIAL PRIMARY KEY,
3
db_name VARCHAR(255) NOT NULL,
4
years_active INT NOT NULL,
5
description VARCHAR(500)
6
);
7
8
INSERT INTO dbs (db_name, years_active, description)
9
VALUES
10
('PostgreSQL', 12, 'A complete PostgreSQL for Developers'),
11
('MySQL', 30, 'MySQL Guide for DBA'),
12
('MariaDB', 9, NULL),
13
('Cassandra', 20, 'Cassandra Beginners');
14
15
SELECT * FROM dbs;
Executing in DbVisualizer, a full-feature database client that supports PostgreSQL and many other databases:
Setting up a demo table
Example #1: Update a Single Record With a New Value
Consider a scenario where we want to update the active years of a database to match the current year. The following statement uses the UPDATE statement to update the db with id 3 by changing the years_active to 10.
1
UPDATE dbs
2
SET years_active = '10'
3
WHERE db_id = 3;
Run the query above in DbVisualizer and you will receive a successful execution message:
By exploring the “Data” tab, you can verify that the update has taken place:
Example #2: Update All Records
Consider another Postgres update scenario where you want to change the value of a column in al records of a table. The following statement uses an UPDATE statement to increase the years_active of all the courses by 2:
1
UPDATE dbs
2
SET years_active = years_active + 2;
Because we did not use a WHERE clause, the UPDATE statement updates all the rows in the dbs table.
Query build successful in DbVisualizer
DbVisualizer shows the affected changes:
Best Practices and Tips for Using the Postgres UPDATE Statement
Below are some of the best practices that should be taken into consideration when working with the UPDATE statement in Postgres:
It’s a wrap!
Conclusion
The Postgres UPDATE statement provides a robust method for updating data in one or more columns of one or more rows in a table. To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including UPDATE operations. Download DbVisualizer for free now!
FAQ
What is the UPDATE statement in PostgreSQL?
The UPDATE statement in PostgreSQL allows you to modify existing data in one or more columns of a table. You can update specific rows based on a condition or modify all rows in the table.
What is the basic syntax for the UPDATE Postgres command?
The basic syntax for the UPDATE Postgres statement is:
1
UPDATE table_name
2
SET column1 = value1,
3
column2 = value2,
4
...
5
WHERE condition;
How can I update multiple columns with a single query in Postgres?
You can specify multiple comma-separated column-value pairs in the SET clause to update several columns at once like this:
1
UPDATE table_name
2
SET column1 = value1,
3
column2 = value2,
4
column3 = value3,
5
...
6
WHERE condition;
How do I update all rows in a PostgreSQL table?
Omit the WHERE clause in a Postgres UPDATE query to update every row in the table. To avoid accidentally updating all data, keep in mind that it is generally safer to use a WHERE clause.
