I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good. However, based on the data I'm querying, we don't have a limit on the early date picker. I could impose an artificial, arbitrary value, but I'd rather have a quick query that fetches the earliest found partition table, possibly the latest as well, without knowing the date ranges of those partitions so we can get the earliest found date.
Partitions are created using a function that I believe is called in a cron job every month that creates a month+1 partition. They are named after timestamps, but only use year and month.
Is there something that allows me do so this (pseudocode):
select min(date_column) from parted_table.earliest_partition;
For example, if a monthly table partition is created every new month starting from 2018, I would expect
earliest_partition to be
select min(date_column) from table_2018_01
How would I get
table_2018_01 without knowing that 2018_01 was the earliest created partition?
Disclosure: I'm working with someone else's code so I am not fully familiar with the system.
tl;dr I guess what I'm looking for is a query that searches table names and returns the earliest one. Should be doable through some built in pg_ table?