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.
Let’s then review the information in the table thus far:
SELECT * FROM books;
This example shows how to use UPDATE to change row with the id=4:
UPDATE books SET title = 'JavaScript' WHERE id = 4;
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.
UPDATE
books
SET
title = ('NodeJs Book'),
price = 30 * 2
WHERE
id = 1;
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;
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;
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.
DELETE FROM books WHERE id=4; SELECT * FROM books;
And let’s check if the line is deleted.
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;
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?
DELETE Vs DROP In SQL
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 FROM books WHERE id=4;
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.
\dt DROP TABLE courses; \dt
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.
Conclusion
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.
