How to check the number of rows inserted into the different partitions in a PostgreSQL partitioned table

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)

How to create a RANGE partitioned table in PostgreSQL

Create the table:

create table album
(
album_id integer generated always as identity,
album_name varchar(40),
genre varchar(20),
label varchar(40),
release_year date
)
partition by range( extract (year from release_year) );

Create a couple of partitions, including a default partition:

create table albums_1974
partition of album
for values from ( '1974' ) to ('1975'); 

create table albums_1979
partition of album
for values from ( '1979' ) to ('1980'); 

create table albums_default 
partition of album DEFAULT;

Another example using a timestamp column as a partition key:

create table shared_docs (
 share_id character      varying(100),
 docid character varying(100),
 identificator character  varying(100),
 package character varying(100),
 fromd timestamp without time zone,
 tod timestamp without time zone,
 rev_year character     varying(100),
 creationtime timestamp without time zone,
 agreedparty character varying(300)
) partition by range (creationtime);

Create the partitions:

create table docs_jan2022 partition of shared_docs for values from    ('2022-01-01 00:00:00') to ('2022-02-01 00:00:00');
create table docs_feb2022 partition of shared_docs for values from    ('2022-02-01 00:00:00') to ('2022-03-01 00:00:00');
create table docs_march2022 partition of shared_docs for values from   ('2022-03-01 00:00:00') to ('2022-04-01 00:00:00');

Yet another example using an integer column:

CREATE TABLE mytable
(
    id bigserial PRIMARY KEY,
    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,
    nk boolean NOT NULL,
    CONSTRAINT mytable_pkey PRIMARY KEY (id,kyear),
    CONSTRAINT key_unique_int UNIQUE (key,kyear)
)
partition by range (kyear);

Create the partitions:

create table mytable_2021
partition of mytable
for values from (2021) to (2022);

create table mytable_2022
partition of mytable
for values from (2022) to (2023);