How to Alter/Update and Delete Data in PostgreSQL

One of the most important tasks that we as database administrators should learn is how to update and delete data in a Postgres database.

So far, we have learned how to filter data, and we will use the same filtering instruction after WHERE to specify the lines whose contents we want to modify or delete.

In this lesson, we’ll look at how to modify and remove users and table columns from a PostgreSQL database. Also, we will have a better understanding of how to alter the database at the end of this session, which will enable you to handle your data more skillfully.

Update Table Row Data in PostgreSQL

We’ll be utilizing the “books” table, that we have created in the Insert Data into Tables article. Let’s use ‘\d‘ to confirm that the table is present and contains the data we anticipate before making any modifications to it.

Update Rows in PostgreSQL
Update Rows in PostgreSQL

Let’s then review the information in the table thus far:

SELECT * FROM books;
Table Information in PostgreSQL
Table Information in PostgreSQL

This example shows how to use UPDATE to change row with the id=4:

UPDATE books SET title = 'JavaScript' WHERE id = 4;  
Use UPDATE to Change Row in PostgreSQL
Use UPDATE to Change Row in PostgreSQL

In this example, we first tell Postgres that we wish to change our data by using the ‘UPDATE‘ command.

The name of the table we want to change is then specified, in this case, it is “books“. Next, we specify the name of the column we want to alter, in this case “title” We specify ‘JavaScript‘ as the new value we wish to add.

With the ‘WHERE‘ clause and a condition that fits the row, we want to edit, like “WHERE id = 4” in this case, we may update only a certain row.

This makes sure that only the book’s title, which has the id 4, and not all the rows, are updated.

If we check our table right now, we can see the following changes:

Here, the name of the book with the id=4 has been changed from “Java” to “JavaScript“.

Update Multiple Table Rows in PostgreSQL

Additionally, it is possible to update many columns simultaneously based on certain criteria, as demonstrated below.

  title = ('NodeJs Book'), 
  price = 30 * 2 
  id = 1;
Update Multiple Rows in PostgreSQL
Update Multiple Rows in PostgreSQL

In the above illustration, we changed the “title” column to include the term “NODE” and the “price” column to be double the initial value (i.e., multiplied by 2) in both cases. Only the rows that met the requirement of “id = 1” received these updates.

To simplify the explanation, we updated the “title” column of the book with id=1 from “PostgreSQL” to “NODE“, and doubled the value in the “price” column for the same row.

These modifications were only applied to the row where id=1. Let’s verify the changes made to the table:

SELECT * FROM books;
Check Table Rows
Check Table Rows

Update Column Value in PostgreSQL

As seen in the following example, we may use the UPDATE statement to change a column’s values using any operation or arithmetic.

UPDATE books SET price = price * 2 WHERE price = 30;
SELECT * FROM books;
Update With Operation in PostgreSQL
Update With Operation in PostgreSQL

We changed the price by multiplying it by 2 where the price is equal to 30.

Delete Rows from Table in PostgreSQL

We can delete a specific row from a table by specifying a condition that uniquely identifies that row. Only that particular row will be deleted, and no other data will be affected.

Using the primary key column as the identifier for the row to be deleted is the best approach since it ensures that we are deleting the correct row.

This is because the primary key has a unique constraint that prevents the creation of two rows with the same value in the primary key column.

Let’s for example delete the line where id is 4.

SELECT * FROM books;

And let’s check if the line is deleted.

Delete Row Data in PostgreSQL
Delete Row Data in PostgreSQL

Different Between ALTER And UPDATE

If you follow along with the lessons you may be confused between the Alter command and update they do the same work so what is the difference?

In PostgreSQL, “UPDATE” and “ALTER” are two different commands used to modify database objects, but they have different purposes.

UPDATE Command in SQL

The UPDATE is used to modify the values of one or more rows in a table. This command allows you to change the values of existing rows in a table based on a specific condition or set of conditions.

For example, you can use the “UPDATE” command to change the value of a column in a table from “A” to “B” for all rows that meet certain criteria. The “UPDATE” command is used to modify the data in a table, rather than the structure of the table itself.

ALTER Command in SQL

The ALTER is used to modify the structure of a table, such as adding or dropping a column, renaming a column or table, or changing the data type of a column.

For example, you can use the “ALTER TABLE” command to add a new column to an existing table, change the data type of a column or rename an existing column.

The “ALTER” command is used to modify the schema of a table, rather than the data stored in the table.

List All Users in PostgreSQL

If you see the lesson where we add a new user to PostgreSQL you may have a question on how we can get and manage all users like add, remove or update a user in case we want to.

In this section, we will learn how to list all users by running the following query.

SELECT usename FROM pg_user;

This will return a list of all users in the database. If you are logged in as a superuser, you can see additional information about each user, such as their login roles and permissions, by executing.

SELECT * FROM pg_user;
Note: This query requires superuser privileges to execute. If you are not a superuser, you may not be able to see all the users and their details.
Delete User From PostgreSQL
Delete User From PostgreSQL

Delete Users In PostgreSQL

Let’s in our example delete the user geeksveda from our postgres server. To do that we can use DROP not DELETE as we mentioned earlier in creating a user in the postgreSQL article. So what is the difference between DELETE and DROP?


Like UPDATE and ALTER the keyword DELETE and DROP are seems to do the same role in Postgres but in reality, there is some difference between them:

DELETE is used to remove rows in a table for example to delete a book in the books table we gonna use delete like this.

Delete Users In PostgreSQL
Delete Users In PostgreSQL

Here if we try with DROP we will get an error because what we need to delete here is just a row from a table.

Whereas DROP is used to delete an entire object like a table database etc. Let’s for example delete a table called courses that we created earlier.

DROP TABLE courses;
Drop Users in PostgreSQL
Drop Users in PostgreSQL

The commands “DROP” or “DELETE” and “ALTER” or “UPDATE” may be a bit confusing as the roles seem to be the same, but if you follow along with what is explained in this article, you will be comfortable disguising and using all of them.


In this section, we cover the concepts of updating and deleting data in a PostgreSQL database. How to modify the contents of a table, the UPDATE command with the SET directive is used.

Following along with this guide and applying what we learned will lead to a better understanding of how to update, delete, and alter work.

If you read this far, tweet to the author to show them you care. Tweet a thanks
Mead Naji is a freelance developer with extensive experience in web development and Linux system administration. Since January 2022, Mead has been working on developing an e-commerce solution with a new payment system like Stripe, as well as doing one-on-one teaching sessions.

Each tutorial at GeeksVeda is created by a team of experienced writers so that it meets our high-quality standards.

Join the GeeksVeda Weekly Newsletter (More Than 5,467 Programmers Have Subscribed)
Was this article helpful? Please add a comment to show your appreciation and support.

Got Something to Say? Join the Discussion...