Database Essentials - Partitioning Tables and Indexes - Manipulating Data

Databases Postgres
1

Let us understand how we can manipulate data for a partitioned table.

Key Concept Explanation

In a partitioned table, we can manipulate data by using the table or the specific partitions created for each subset of data. Here are some key concepts to note:

Insert Data

  • We can insert data using the main table or table specifically created for a partition.
  • For a partitioned table users_part, we can use either the table name users_part or the partition name users_part_u to insert records with user_role ‘U’.
CREATE TABLE users_part_u 
PARTITION OF users_part  
FOR VALUES IN ('U')

Update Data

  • Changing the value in a partitioned column that results in moving data from one partition to another is handled internally.
  • The update operation remains the same regardless of the partitioning strategy used (list, range, or hash).

Delete Data

  • Data can be deleted from the main table or the tables created for each partition (users_part, users_part_u, users_part_a, etc).

Hands-On Tasks

Here are some hands-on tasks you can perform to manipulate data for a partitioned table:

  1. Truncate the users_part table.
  2. Insert records into the users_part table.
  3. View data from the users_part_u partition.
  4. Insert records into the users_part_a partition.
  5. View data from the users_part table.
  6. Update user roles in the users_part table.
  7. View data from the users_part_a partition.
  8. Delete a record from the users_part table.
  9. Delete a record from the users_part_u partition.
  10. View data from the users_part table.

Conclusion

In this article, we learned about manipulating data in a partitioned table by inserting, updating, and deleting records in both the main table and the partitions. It’s essential to understand how to manage data within a partitioned table to maintain an efficient and organized database.

Link to the YouTube video

Remember, practice makes perfect, so feel free to try out these tasks and engage with the community for further learning.

Watch the video tutorial here