How to Use the PostgreSQL Update Statement
Introduction
If you’re getting started with PostgreSQL, it’s important to learn how to perform basic database operations such as inserting, updating and deleting records. In this tutorial, we’ll focus our attention on the PostgreSQL UPDATE statement, which allows users to modify existing PostgreSQL records. Let’s take a closer look at the UPDATE
statement and review some examples of how it’s used.
Prerequisites
Before you attempt to follow along with the examples in this article, make sure that the following prerequisites are in place:
PostgreSQL must be properly installed and working. If you need to confirm whether PostgreSQL is already installed, the command
psql -V
will display the version of PostgreSQL on your machine.You’ll need to have a working knowledge of database management systems and basic SQL commands.
Open psql command line interface
Our first task will be to connect to the psql
command-line console.
The following command will grant you the login privileges for the postgres
superuser after you enter the password:
1 | sudo su - postgres |
You can then start psql
:
1 | psql |
Once you’re connected to the psql interface, you’ll be able to execute various PostgreSQL commands and queries.
Create a PostgreSQL database
Before we can update data in a table, we’ll need to create a database. To create it, we’ll use the command shown below:
1 | CREATE DATABASE test; |
NOTE: Keep in mind that you can name your database anything you want.
Create a PostgreSQL table
Next, we’ll create a table. This PostgreSQL table will hold the records that we’ll update later on. Here’s the basic syntax for the CREATE TABLE
statement:
1 2 | CREATE TABLE TABLE_NAME( COLUMN_NAME + DATATYPE + CONSTRAINTS [OPTIONAL]); |
When you create a table in PostgreSQL, you may define many columns separated by commas.
The SQL statement used to create our table will look like the following:
1 2 3 4 5 6 | test=# CREATE TABLE grade( student_id INT NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL, grades REAL, remarks TEXT ); |
Insert data in a Table
Now that we have a test database and a table, let’s insert some records. The following syntax is used to insert records into a PostgreSQL table:
1 2 | INSERT INTO TABLE_NAME(column1, column2,....) VALUES(value1, value2,....) |
Our INSERT
statement will look like this:
1 2 | INSERT INTO grade(student_id, name, grades, remarks) VALUES (1, 'Justine', '1.5', 'Pass'); |
To insert data, we identify the table_name
after the INSERT INTO
clause, followed by the list of columns separated by commas. We then list all the respective values for those columns, again separated by commas.
After inserting data, you can use the SELECT
statement to see all the records in your table:
1 | SELECT * FROM grade; |
The results will look like the following:
1 2 3 4 5 6 7 8 | student_id | name | grades | remarks ------------+---------+--------+--------- 1 | Justine | 1.5 | Pass 2 | Mia | 1.25 | Pass 3 | Taylor | 3.5 | Failed 4 | Kobby | 1 | Pass 5 | Kyline | 3 | Failed (5 ROWS) |
Update the PostgreSQL table
The PostgreSQL UPDATE
statement is used to modify existing records in a table. To update specific rows, you need to use the WHERE
clause. Otherwise, every row in the table would be updated.
We use this basic syntax to update a table:
1 2 3 | UPDATE TABLE_NAME SET column1 = 'new_value' column2 = 'new_value' WHERE [condition]; |
When you construct an UPDATE
statement, you must first specify the table_name
after the UPDATE
clause. You would then list the columns whose values you want to change under the SET
clause. You can update the values in multiple columns, as long as they’re separated by commas.
Let’s check out an example of the PostgreSQL UPDATE
statement. The following SQL statement will update, or modify, any Postgres records that have a student_id
of 3
:
1 2 | UPDATE grade SET grades = '2', remarks = 'Pass' WHERE student_id = 3; |
Since student_id
is an ID column with a PRIMARY KEY
constraint, only one record should have been updated:
1 | UPDATE 1 |
Let’s verify that our UPDATE
operation was successful:
1 2 3 4 5 | test=# SELECT name, grades, remarks FROM grade WHERE student_id = 3; name | grades | remarks --------+--------+--------- Taylor | 2 | Pass (1 ROW) |
Update all Postgres table records in a column
It’s also possible to update all of the table rows in a certain column in PostgreSQL. We can do this by passing the column name to the UPDATE
clause, as seen in the following example:.
1 | UPDATE grade SET remarks = 'Pass'; |
This UPDATE
statement should return the response shown below, which indicates that five records were updated:
1 | UPDATE 5 |
Now that we updated all the records in our table, let’s try executing this query: SELECT remarks FROM grade;
. The result will contain the values in the remarks
column. You’ll see that they’ve all been updated with the value of Pass
:
1 2 3 4 5 6 7 8 | remarks --------- Pass Pass Pass Pass Pass (5 ROWS) |
Conclusion
When you’re working with data stored in PostgreSQL, you’ll find yourself needing to know how to update existing records in a table. In this article, we discussed the PostgreSQL UPDATE statement and provided multiple examples of its use. With our instructions and examples, you’ll be prepared to modify records in your own PostgreSQL tables.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started