Monthly partitioning with integer column

[vnirocha]

Hi!

I need to monthly partition the zabbix trends table, which I've simplified here:

CREATE TABLE IF NOT EXISTS zabbix.partmantesting_3
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

But clock is defined as an integer, and when I try to create_parent( ) using p_interval as 1 month:

SELECT partman.create_parent(p_parent_table := 'zabbix.trends', p_control := 'clock',
p_interval := '1 month', p_type := 'range', p_start_partition := null, p_premake := 3,
p_automatic_maintenance := 'on', p_constraint_cols := null, p_jobmon := true, p_date_trunc_interval := 'months',
p_template_table := null );

it gives me the following error

ERROR:  invalid input syntax for type bigint: "1 month"
CONTEXT: PL/pgSQL function partman.create_parent(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text) line 448 at assignment

It works fine on daily partitions, because I set p_interval to '86400' seconds in a day. But month does not have a predefined amount of seconds. Some months have more days than others.

What should I do in this situation?

pg_partman 5.2.2
postgres 16.6

[keithf4]

See the p_epoch parameter to create_parent. Zabbix was the reason this feature was added. :)

https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md#create_parent

[vnirocha]

Saved my day! Thank you so much @keithf4