List Partitioning
Let us understand how we can take care of list partitioning of tables.
-
It is primarily used to create partitions based up on the values.
-
Here are the steps involved in creating table using list partitioning strategy.
-
Create table usingÂ
PARTITIONÂ BYÂ LIST -
Add default and value specific partitions
-
Validate by inserting data into the table
-
-
We can detach as well as drop the partitions from the table.
Create Partitioned Table¶
Let us create partitioned table with name users_part.
-
It contains same columns asÂ
users. -
We will partition based up onÂ
user_role field.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
%sql DROP TABLE IF EXISTS users
Done.
[]
%%sql
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', --U and A
is_active BOOLEAN DEFAULT FALSE,
created_dt DATE DEFAULT CURRENT_DATE,
last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%sql DROP TABLE IF EXISTS users_part
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
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', --U and A
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)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
CREATE INDEX users_part_email_id_idx
ON users_part(user_email_id)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
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')
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
---------------------------------------------------------------------------
CheckViolation Traceback (most recent call last)
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1276 self.dialect.do_execute(
-> 1277 cursor, statement, parameters, context
1278 )
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
592 def do_execute(self, cursor, statement, parameters, context=None):
--> 593 cursor.execute(statement, parameters)
594
CheckViolation: no partition of relation "users_part" found for row
DETAIL: Partition key of the failing row contains (user_role) = (U).
The above exception was the direct cause of the following exception:
IntegrityError Traceback (most recent call last)
<ipython-input-23-b06b3c83cab2> in <module>
----> 1 get_ipython().run_cell_magic('sql', '', "nINSERT INTO users_part (user_first_name, user_last_name, user_email_id)nVALUES n ('Scott', 'Tiger', 'scott@tiger.com'),n ('Donald', 'Duck', 'donald@duck.com'),n ('Mickey', 'Mouse', 'mickey@mouse.com')n")
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
2369 with self.builtin_trap:
2370 args = (magic_arg_s, cell)
-> 2371 result = fn(*args, **kwargs)
2372 return result
2373