Mastering Data Manipulation in PostgreSQL

Introduction

PostgreSQL, the top open-source database management system, offers a wide selection of commands to manage data manipulation tasks effectively. This blog will go over essential data manipulation methods and provide examples using PostgreSQL.

1. Inserting Data:
PostgreSQL provides the capability of putting data into a database using the INSERT command. If we take the 'products' table as an example, which contains columns like 'product_no,' 'name,' and 'price,' then it must be populated with data before becoming useful.

Example 1: Insert a single row with values explicitly listed.

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);

Example 2: Insert multiple rows in a single command.

INSERT INTO products (product_no, name, price) VALUES
    (3, 'Milk', 2.99),
    (4, 'Eggs', 3.49),
    (5, 'Butter', 4.29);

2. Updating Data:
PostgreSQL offers the UPDATE command to alter any existing data in a table. This command requires you to specify the particular table, column, and new value that you want to update as well as provide a condition that states which rows should be updated.

Example 3: Update multiple columns simultaneously.

UPDATE products SET name = 'Cheddar', price = 11.99 WHERE product_no = 1;

3. Deleting Data:
PostgreSQL provides the DELETE command as an option for deleting unwanted data. It requires a condition to be specified, so that it can accurately determine which rows to delete from a table.

Example 4: Delete all products with a price of 0.

DELETE FROM products WHERE price = 0;

4. Returning Data from Modified Rows:
The RETURNING clause in INSERT, UPDATE, and DELETE commands enables the retrieval of data from records that have been altered. This is advantageous when it is necessary to access the results of a modification without having to conduct extra queries.

Example 5: Use RETURNING with UPDATE to retrieve updated data.

UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price;

Conclusion

PostgreSQL offers robust data management capabilities, allowing users to easily add, modify, and remove information. Having a firm understanding of these commands is critical for using PostgreSQL productively. With an adept knowledge of data manipulation in PostgreSQL, you can be sure to address any database task confidently and unlock the full capability of this flexible open-source system.

Reference

https://www.postgresql.org/docs/current/dml.html