====== Partitioning ======

===== List =====
<code SQL>
CREATE TABLE USERS_PART (
   user_id serial,
   username varchar(50),
   user_role varchar(1),
   created_date DATE DEFAULT CURRENT_DATE,
   last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY LIST (user_role);

-- Inserts would currently fail as we haven't defined any partitions for this table

CREATE TABLE USERS_PART_A
PARTITION OF USERS_PART 
FOR VALUES IN ('A');

-- Inserts will now only work with a user_role of A
</code>

===== Range =====
<code SQL>
CREATE TABLE USERS_PART (
   user_id serial,
   username varchar(50),
   user_role varchar(1),
   created_date DATE DEFAULT CURRENT_DATE,
   last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_date);

-- Inserts would currently fail as we haven't defined any partitions for this table

CREATE TABLE USERS_PART_2021
PARTITION OF USERS_PART
FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

-- Inserts will now only work when the created date is in 2021
</code>

===== Default =====

From Postgres 11 it is possible to create a default partition. \\
<code SQL>
CREATE TABLE USERS_PART_DF PARTITION OF USERS_PART DEFAULT;
</code>

===== Select / Insert =====

It is possible to show which partitioned table your select or insert query has used. \\
<code SQL>
SELECT tableoid, tableoid::regclass, * FROM users_part
 WHERE user_role = 'A';
</code>

<code SQL>
INSERT INTO users_part (username, user_role) 
VALUES ('bloggs01', 'A') 
RETURNING tableoid::regclass, user_id;
</code>

===== Example =====
[[PGPartitionExample| Partitioning Example]]\\