19 - Data Modification in PostgreSQL

Introduction to Data Modification in PostgreSQL

Modifying data in a PostgreSQL database is a critical aspect of database management. It involves the insertion, updating, and deletion of records in database tables. In this guide, we will explore the methods and SQL statements used for data modification in PostgreSQL, with examples and practical applications.

Inserting Data

Inserting data into a PostgreSQL table is the process of adding new records to the database. The INSERT statement is used for this purpose, and it can be applied in various ways.

Basic INSERT Statement

The basic syntax of the INSERT statement is as follows:


INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

For example, to insert a new employee into the ’employees’ table:


INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (101, 'John', 'Doe', 55000);

This query inserts a new record with an ’employee_id’ of 101, ‘first_name’ of ‘John’, ‘last_name’ of ‘Doe’, and a ‘salary’ of 55000 into the ’employees’ table.

Inserting Data from Another Table

You can also insert data from one table into another using the INSERT INTO … SELECT statement. For example, to copy all orders with ‘order_date’ in the year 2023 from the ‘orders’ table to the ‘archive_orders’ table:


INSERT INTO archive_orders (order_id, order_date, customer_id, total_amount)
SELECT order_id, order_date, customer_id, total_amount
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

This query inserts records from the ‘orders’ table into the ‘archive_orders’ table for orders placed in 2023.

Updating Data

Updating data in a PostgreSQL table is essential for modifying existing records. The UPDATE statement is used for this purpose.

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 also be used to update data based on specific conditions. For example, to 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.

Deleting Data

Deleting data in a PostgreSQL table removes specific records from the database. The DELETE statement is used for this purpose.

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, to 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.

Conclusion

Data modification is a fundamental aspect of database management in PostgreSQL. The INSERT, UPDATE, and DELETE statements provide the means to add, modify, and remove records from database tables. Whether you are adding new data, updating existing records, or deleting outdated information, understanding these SQL statements is crucial for effective database maintenance and data management.

19 – Data Modification in PostgreSQL