Create the table:
CREATE TABLE mytable
(
id bigserial,
key text COLLATE pg_catalog."default" NOT NULL,
outdata jsonb,
partno integer,
koffset bigint,
handled_at timestamp with time zone,
inserted_at timestamp with time zone NOT NULL DEFAULT now(),
kyear integer NOT NULL, -- Ensure it's NOT NULL to work properly with partitions
nk boolean NOT NULL,
CONSTRAINT mytable_pkey PRIMARY KEY (id,kyear)
)
PARTITION BY RANGE (kyear);
Create the partitions. Make it suitable for ranges that adhere to the standard YYYYMM:
CREATE TABLE mytable_202501
PARTITION OF mytable
FOR VALUES FROM (202501) TO (202502);
CREATE TABLE mytable_202502
PARTITION OF mytable
FOR VALUES FROM (202502) TO (202503);
Insert some test values:
INSERT INTO mytable (key, outdata, partno, koffset, handled_at, kyear, nk)
VALUES
('A123', '{"data": "test1"}', 101, 500, NOW(), 202501, TRUE),
('B456', '{"data": "test2"}', 102, 600, NOW(), 202501, FALSE),
('C789', '{"data": "test3"}', 103, 700, NOW(), 202501, TRUE);
INSERT INTO mytable (key, outdata, partno, koffset, handled_at, kyear, nk)
VALUES
('D111', '{"data": "test4"}', 104, 800, NOW(), 202502, FALSE),
('E222', '{"data": "test5"}', 105, 900, NOW(), 202502, TRUE),
('F333', '{"data": "test6"}', 106, 1000, NOW(), 202502, FALSE);
Confirm that the data is indeed there:
select * from mytable;
Finally, verify that the rows have been commited to two different partitions:
musicdb=> SELECT relname, n_tup_ins
musicdb-> FROM pg_stat_all_tables
musicdb-> WHERE relname IN ('mytable_202501', 'mytable_202502');
relname | n_tup_ins
----------------+-----------
mytable_202501 | 3
mytable_202502 | 3
(2 rows)
