Let us understand how we can take care of list partitioning of tables.
-
It is primarily used to create partitions based on the values.
-
Here are the steps involved in creating a table using the list partitioning strategy.
- Create a table using
PARTITION BY LIST
. - Add default and value-specific partitions.
- Validate by inserting data into the table.
- Create a table using
-
We can detach as well as drop the partitions from the table.
Create Partitioned Table
Let us create a partitioned table with the name users_part
.
- It contains the same columns as
users
. - We will partition based on the
user_role
field.
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN DEFAULT FALSE,
user_password VARCHAR(200),
user_role VARCHAR(1) NOT NULL DEFAULT 'U',
is_active BOOLEAN DEFAULT FALSE,
created_dt DATE DEFAULT CURRENT_DATE,
last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users_part (
user_id SERIAL,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN DEFAULT FALSE,
user_password VARCHAR(200),
user_role VARCHAR(1) NOT NULL DEFAULT 'U',
is_active BOOLEAN DEFAULT FALSE,
created_dt DATE DEFAULT CURRENT_DATE,
last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_role, user_id)
) PARTITION BY LIST(user_role);
CREATE INDEX users_part_email_id_idx
ON users_part(user_email_id);
-- Below INSERT statement will fail if we attempt to execute it here
INSERT INTO users_part (user_first_name, user_last_name, user_email_id)
VALUES
('Scott', 'Tiger', 'scott@tiger.com'),
('Donald', 'Duck', 'donald@duck.com'),
('Mickey', 'Mouse', 'mickey@mouse.com');