21 - Updating and Deleting Data in PostgreSQL

Introduction to Updating and Deleting Data in PostgreSQL

Updating and deleting data in a PostgreSQL database are essential operations for maintaining the accuracy and integrity of your data. These actions allow you to modify or remove existing records in your database tables. In this guide, we’ll explore the methods for updating and deleting data in PostgreSQL, along with practical examples and use cases.

Updating Data

Updating data in PostgreSQL involves modifying the values of existing records in a table. The UPDATE statement is used for this purpose and allows you to specify which records to change and the new values to set.

Basic UPDATE Statement

The basic syntax of the UPDATE statement is as follows:


UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example, to update the salary of an employee with ’employee_id’ 101 in the ’employees’ table:


UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

This query updates the salary of the employee with ’employee_id’ 101 to 60000.

Updating Data Using Subqueries

Subqueries can be used in combination with the UPDATE statement to update data based on specific conditions. For example, you can update the ‘discount’ column in the ‘products’ table by decreasing the price of products with a price above $100 by 10%:


UPDATE products
SET discount = 10
WHERE price > 100;

This query updates the ‘discount’ column for products with a price above $100.

Updating Data with Default Values

PostgreSQL allows you to update data with default values when you don’t specify new values for all columns. For example, consider a ‘tasks’ table with a ‘status’ column that has a default value of ‘Pending.’ When you update a task without specifying the ‘status,’ it will use the default value:


-- Create a table with a default value for 'status'
CREATE TABLE tasks (
    task_id serial PRIMARY KEY,
    task_description VARCHAR,
    status VARCHAR DEFAULT 'Pending'
);

-- Update a task with a new description and use the default status.
UPDATE tasks
SET task_description = 'New Task Description'
WHERE task_id = 101;

In this example, the ‘status’ column will use the default value ‘Pending’ because it was not explicitly updated in the query.

Deleting Data

Deleting data in PostgreSQL involves removing specific records from a table. The DELETE statement is used for this purpose, and it allows you to specify which records to delete based on conditions.

Basic DELETE Statement

The basic syntax of the DELETE statement is as follows:


DELETE FROM table_name
WHERE condition;

For example, to delete an employee with ’employee_id’ 101 from the ’employees’ table:


DELETE FROM employees
WHERE employee_id = 101;

This query deletes the employee with ’employee_id’ 101 from the ’employees’ table.

Deleting Data Using Subqueries

Subqueries can also be used with the DELETE statement to remove data based on specific conditions. For example, you can delete all orders from the ‘orders’ table where the associated customer has made no purchases:


DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM order_items));

This query deletes orders for customers who have made no purchases, using a subquery to identify such customers.

Deleting Data with CASCADE Deletion

PostgreSQL allows you to configure the database schema to perform cascade deletions, which automatically delete related records in child tables when a record in a parent table is deleted. This is helpful for maintaining referential integrity. For example, if you have an ‘authors’ table and a ‘books’ table with a foreign key relationship, you can set up cascade deletion as follows:


-- Configure cascade deletion
CREATE TABLE authors (
    author_id serial PRIMARY KEY,
    author_name VARCHAR
);

CREATE TABLE books (
    book_id serial PRIMARY KEY,
    book_title VARCHAR,
    author_id INT REFERENCES authors(author_id) ON DELETE CASCADE
);

-- When an author is deleted, all associated books are deleted as well.
DELETE FROM authors WHERE author_id = 101;

In this example, when an author is deleted, all associated books are also deleted automatically due to the CASCADE option defined in the foreign key relationship.

Conclusion

Updating and deleting data in PostgreSQL are essential operations for maintaining a well-organized and accurate database. The UPDATE and DELETE statements allow you to modify and remove records based on specific conditions, while subqueries provide the flexibility to handle more complex data manipulation tasks. Understanding how to effectively update and delete data is crucial for maintaining data quality and integrity in your PostgreSQL database.

21 – Updating and Deleting Data in PostgreSQL