Postgres: List all RANGE partitions, sorted by their range start value

My table contains 180 partitons, and looks like this when described in psql:

mydb=> \d mytable
      Partitioned table "myschema.mytable"
      Column       |           Type           | Collation | Nullable |           Default            
-------------------+--------------------------+-----------+----------+------------------------------
 id                | bigint                   |           | not null | generated always as identity
 ad_updtud_log_id  | bigint                   |           | not null | 
 period            | integer                  |           | not null | 
 created           | timestamp with time zone |           | not null | 
Partition key: RANGE (id)
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id, periode)
    "mytable_id_index" btree (im_oppsum_logg_id)
    "mytable_opprettet_index" btree (opprettet)
Foreign-key constraints:
    "fk_mytable" FOREIGN KEY (ad_updtud_log_id, period) REFERENCES mytable2(id, period)
Number of partitions: 180 (Use \d+ to list them.)

This is how you can list all partitions belonging to a range-partitioned table in postgreSQL, based on the values:

SELECT
    child.relname AS partition_name,
    regexp_replace(pg_get_expr(child.relpartbound, child.oid), '.*FROM \(''(\d+).*', '\1')::bigint AS range_start,
    pg_get_expr(child.relpartbound, child.oid) AS partition_range
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nm ON nm.oid = child.relnamespace
WHERE parent.relname = 'mytable'
  AND nm.nspname = 'myschema'
ORDER BY range_start;

Result (excerpt only):

   partition_name   | range_start | partition_range                  
---------------------------------------+-------------+--------------------------------------------
 mytable_0	    |           0 | FOR VALUES FROM ('0') TO ('30000000')
 mytable_30000000   |    30000000 | FOR VALUES FROM ('30000000') TO ('60000000')
 mytable_60000000   |    60000000 | FOR VALUES FROM ('60000000') TO ('90000000')
 mytable_90000000   |    90000000 | FOR VALUES FROM ('90000000') TO ('120000000')
 mytable_120000000  |   120000000 | FOR VALUES FROM ('120000000') TO ('150000000')
 mytable_150000000  |   150000000 | FOR VALUES FROM ('150000000') TO ('180000000')
 mytable_180000000  |   180000000 | FOR VALUES FROM ('180000000') TO ('210000000')
 mytable_210000000  |   210000000 | FOR VALUES FROM ('210000000') TO ('240000000')
 mytable_240000000  |   240000000 | FOR VALUES FROM ('240000000') TO ('270000000')
 mytable_270000000  |   270000000 | FOR VALUES FROM ('270000000') TO ('300000000')
 mytable_300000000  |   300000000 | FOR VALUES FROM ('300000000') TO ('330000000')
 mytable_330000000  |   330000000 | FOR VALUES FROM ('330000000') TO ('360000000')
 mytable_360000000  |   360000000 | FOR VALUES FROM ('360000000') TO ('390000000')
 mytable_390000000  |   390000000 | FOR VALUES FROM ('390000000') TO ('420000000')
 mytable_420000000  |   420000000 | FOR VALUES FROM ('420000000') TO ('450000000')
 mytable_450000000  |   450000000 | FOR VALUES FROM ('450000000') TO ('480000000')
 

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);