Let us understand how to manage partitions for a partitioned table using users_part
.
- All users data with
user_role
as ‘U’ should go to one partition by the nameusers_part_u
. - All users data with
user_role
as ‘A’ should go to one partition by the nameusers_part_a
. - We can add a partition to an existing partitioned table using
CREATE TABLE partition_name PARTITION OF table_name
. - We can have a partition for default values so that all the data that does not satisfy the partition condition can be added to it.
- We can have a partition for each value or for a set of values.
- We can have one partition for
U
as well asA
and the default partition for all other values. - We can have individual partitions for
U
,A
respectively and default partition for all other values. - We can use
FOR VALUES IN (val1, val2)
as part ofCREATE TABLE partition_name PARTITION OF table_name
to specify values for the respective table created for the partition.
- We can have one partition for
- Once partitions are added, we can insert data into the partitioned table.
- We can detach using
ALTER TABLE
and drop the partition or drop the partition directly. To drop the partition, we need to use theDROP TABLE
command.
Here is how we can create a partition for default values for a list partitioned table **users_part**.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@pg.itversity.com:5432/itversity_sms_db
%%sql
CREATE TABLE users_part_default
PARTITION OF users_part DEFAULT
All the 3 records will go to the default partition as we have not defined any partition for user_role 'U'.
%%sql
INSERT INTO users_part (user_first_name, user_last_name, user_email_id, user_role)
VALUES
(‘Scott’, ‘Tiger’, ‘scott@tiger.com’, ‘U’),
(‘Donald’, ‘Duck’, ‘donald@duck.com’, ‘U’),
(‘Mickey’, ‘Mouse’, ‘mickey@mouse.com’, ‘U’)
%%sql
SELECT * FROM users_part_default
%%sql
CREATE TABLE users_part_a
PARTITION OF users_part
FOR VALUES IN (‘A’)
%%sql
UPDATE users_part
SET
user_role = ‘A’
WHERE user_email_id = ‘scott@tiger.com’
%%sql
SELECT * FROM users_part
%%sql
SELECT * FROM users_part_a
%%sql
SELECT * FROM users_part_default
This will fail as there are records with user_role 'U' in the default partition.
%%sql
CREATE TABLE users_part_u
PARTITION OF users_part
FOR VALUES IN (‘U’)
We can detach the partition, add a partition for 'U' and load the data from the detached partition into the new partition created.
%%sql
ALTER TABLE users_part
DETACH PARTITION users_part_default
%%sql
CREATE TABLE users_part_u
PARTITION OF users_part
FOR VALUES IN (‘U’)
%%sql
INSERT INTO users_part
SELECT * FROM users_part_default
%%sql
SELECT * FROM users_part_a
%%sql
SELECT * FROM users_part_u
We can drop and create a partition for default or truncate and attach the existing default partition.
%%sql
DROP TABLE users_part_default
%%sql
CREATE TABLE users_part_default
PARTITION OF users_part DEFAULT