You Are Here
admin

postgresql sql query to get names of table partitions containing 3 month old data

Finance Crypto

I am looking for postgresql sql query: to get names of table partitions containing 3 month old data

I have following example table:

CREATE TABLE sales (id int, p_name text, amount int, sale_date date) PARTITION BY RANGE (sale_date);


CREATE TABLE sales_2021_M01 PARTITION OF sales FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE sales_2021_M02 PARTITION OF sales FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE sales_2021_M03 PARTITION OF sales FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE sales_2021_M04 PARTITION OF sales FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE sales_2021_M05 PARTITION OF sales FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE sales_2021_M06 PARTITION OF sales FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE sales_2021_M07 PARTITION OF sales FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE sales_2021_M08 PARTITION OF sales FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE sales_2021_M09 PARTITION OF sales FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE sales_2021_M10 PARTITION OF sales FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE sales_2021_M11 PARTITION OF sales FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE sales_2021_M12 PARTITION OF sales FOR VALUES FROM ('2021-12-01') TO ('2022-01-0