Differences

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

--- postgresqlpartioning [2021/07/26 16:58] – [List] z0hpvk
+++ postgresqlpartioning [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 9: / Line 9: @@
    created_date DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-) PARTITION BY LIST(user_role);
+) PARTITION BY LIST (user_role);
 -- Inserts would currently fail as we haven't defined any partitions for this table
-CREATE TABLE USERS_PART_DEFAULT
+CREATE TABLE USERS_PART_A
 PARTITION OF USERS_PART
-DEFAULT;
+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]]\\