PostgreSQL offers various built-in commands to perform table operations, such as INSERT, DELETE, UPDATE, ALTER TABLE, etc. Updating tables in Postgres is one of the most frequently used operations that assist us in maintaining and organizing table data. Whether you need to modify an existing column, add a new one, or delete an old one, it's important to know how to do so efficiently and effectively.

With this tutorial, you'll learn how to update an existing table in Postgres and be well on your way to managing and organizing your data efficiently. In this regard, the following topics will be covered in this write-up:

• How to Update an Existing Table in Postgres?
• What Does ALTER TABLE Statement Do in Postgres?
• What Does UPDATE Statement Do in Postgres?

So, let’s start!

How to Update/Modify an Existing Table in PostgreSQL?

Postgres allows us to update the structure of an already existing table and the data/records of an existing table using different commands. For instance, commands like ALTER TABLE, ADD COLUMN, RENAME TABLE, etc., are used to modify the table's structure in Postgres. On the other hand, to modify the table’s records, the UPDATE statement with the SET clause is used in Postgres.

What Does ALTER TABLE Statement Do in Postgres?

In Postgres, the ALTER TABLE command modifies the existing table's structure. For instance, using ALTER TABLE statement, you can perform the following functionalities:

- Add a New Column.
- Drop Column.
- Alter Column Type.
- Rename Column.
- Rename Table.
- Add/Drop Constraints.
- Set Column’s Default Value.

Let’s learn how to update the table’s structure via the following examples.

Example 1: Renaming a Postgres Table

We have already created a table named “shortlisted_students”, whose details are shown in the following snippet:

The below-provided command will rename the “shortlisted_student” table to “shorlisted_candidates” via the ALTER TABLE statement:

ALTER TABLE shortlisted_students
RENAME TO shortlisted_candidates;

Execute the SELECT * command followed by the new table name, i.e., “shortlisted_candidates”, to verify the working of the ALTER TABLE statement:

Output proves the working of the ALTER TABLE command. Now fetching the table’s data with the old name, i.e., “shorlisted_students”, will throw an error:

The above snippet shows an error because the “shortlisted_students” table has been renamed to “shortlisted_candidates”, and there is no such table with the name “shortlisted_students”.

Example 2: Set Default Value for a Column

We have already created a table named “product_details”, whose details are described in the following snippet:

The output snippet shows that the DEFAULT value is not set for any column. Let’s learn how to set a default value for an existing column using the ALTER TABLE statement:

ALTER TABLE product_details 
ALTER COLUMN purchase_date SET DEFAULT CURRENT_DATE;

The “CURRENT_DATE” has been set as the default value for the “purchase_date” column. Now insert a new record into the product_details table to comprehend the working of the DEFAULT keyword:

INSERT INTO product_details(product_id, product_price)
VALUES (1, 5000);

Let’s verify the table’s data via the “SELECT *” command:

The output snippet clarifies that the current date has been inserted into the purchase_date column by default, which proves the DEFAULT keyword's working.

Note: Similarly, you can use the ALTER TABLE command to update the column name, column data type, add/drop a specific column, and so on.

What Does UPDATE Statement Do in Postgres?

In Postgres, you must execute the UPDATE statement to update the table's record. For this purpose, use the following syntax for the UPDATE statement:

UPDATE table_name
SET column_1 = val_1, column_2 = val_2, …, column_N = val_N
WHERE condition;

- Specify the table’s name in place of table_name.
- Use the SET clause to specify the columns you want to update and the new values.
- Comma-separated syntax in Postgres allows us to update multiple columns at once.
- WHERE is an optional clause that specifies the condition to determine which rows should be updated.
- Skipping the WHERE clause will modify all the rows in the table.

Example: How to Update Table’s Data in Postgres?

We have already created a table named “article_details”, whose data is shown in the following snippet:

Suppose we want to update the “article_title” from “PostgreSQL UPDATE Query” to “PostgreSQL UPDATE STATEMENT”. For this purpose, we will execute the Update statement as follows:

UPDATE article_details
SET article_title = 'PostgreSQL UPDATE Statement'
WHERE article_id = 10;

The output snippet shows that the UPDATE statement was executed successfully. You can verify the updated record using the “SELECT *” query:

SELECT * FROM article_details;

This is how you can update the data of an existing table in Postgres.

Conclusion

In PostgreSQL, the ALTER TABLE command updates the table’s structure, such as adding a new column, renaming a column, changing data type, etc. While modifying the table’s records can be accomplished in Postgres via the UPDATE query and the SET clause. This blog post presented a detailed guide for updating an existing Postgres table via practical examples.