Postgress pg_partman weekly partitioning, change week start day

Question

I am using pg_partman on PostgreSQL DB, for creating auto partitioning for weekly data. The weeks are always created from Monday to Monday.

This is the query I am using:

SELECT partman.create_parent(p_parent_table => 'schema.table',
                             p_control => 'start_week_date',
                             p_type => 'native',
                             p_interval => 'weekly',
                             p_premake => 1);

This is the DDL generated by pg_partman:

partition of table
(
    constraint table_p2021_09_pkey
        primary key (id, start_week_date)
)
FOR VALUES FROM ('2021-10-25') TO ('2021-11-01');

I want the partition to be from Saturday to Saturday. Is it possible?

Answer 1

You may be able to get what you want by setting p_start_partition to be a Saturday. See p_date_trunc_interval in the documentation:

By default, pg_partman's time-based partitioning will truncate the child table starting values to line up at the beginning of typical boundaries (midnight for daily, day 1 for monthly, Jan 1 for yearly, etc). If a custom time interval that does not fall on those boundaries is desired, this option may be required to ensure the child table has the expected boundaries (especially if you also set p_start_partition).