CODEWITHSUNDEEP
sqlpostgresqldatabase-partitioning
Hannes De Smet
Hannes De Smet

Reputation: 55

Partition DB using the weeknumber of date column

So I want to partition my Postgres (v11) DB into several partitions. I have figured out how to do it by using a range of my dates.

CREATE TABLE test(
some_id  int,
some_date date,
some_value int,
) PARTITION BY RANGE (some_date);

However I would like to partition based on the week-number of the date. I tried the following code without success:

CREATE TABLE test(
some_id  int,
some_date date,
some_value int,
) PARTITION BY DATE_PART('week', some_date);

Anybody any idea?

Upvotes: 0

Views: 1031

Answers (1)

AdamKG
AdamKG

Reputation: 14046

This is still range partitioning, you're just using a single-integer range. (List partitioning would also work fine here.) Here's an abbreviated example showing week-of-year partitioning - IRL, you'd have 52 partitions covering weeks 1-52 instead of the 1, 2, 3-52 that I show here.

testdb=# CREATE TABLE test(                                                     some_id  int,
some_date date,
some_value int
) PARTITION BY range(DATE_PART('week', some_date));
CREATE TABLE
testdb=# create table test_week01 partition of test for values from (1) to (2);
CREATE TABLE
testdb=# create table test_week02 partition of test for values from (2) to (3);
CREATE TABLE
testdb=# create table test_week_rest partition of test for values from (3) to (MAXVALUE);
CREATE TABLE
testdb=# insert into test select 1, '2022-01-03', 2;
INSERT 0 1
testdb=# insert into test select 3, '2022-01-10', 4;
INSERT 0 1
testdb=# insert into test select 5, '2022-05-01', 6;
INSERT 0 1
testdb=# select tableoid::regclass, * from test;
    tableoid    | some_id | some_date  | some_value 
----------------+---------+------------+------------
 test_week01    |       1 | 2022-01-03 |          2
 test_week02    |       3 | 2022-01-10 |          4
 test_week_rest |       5 | 2022-05-01 |          6
(3 rows)

Upvotes: 1

Related Questions