Differences

This shows you the differences between two versions of the page.

--- postgresqlpartioning [2021/07/26 16:49] – created z0hpvk
+++ postgresqlpartioning [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== 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]]\\