Postgresql – way to search the earliest or latest postgres partition for table

partitioningpostgresqlquery

BACKGROUND

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.

PROBLEM

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 table_2018_01

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.

postgres 9.4

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?

Best Answer

First off, many thanks to those who commented and helped me get to this answer. I was not as detailed as I should have been in posting the question. I believe this is the answer I seek:

select table_name from information_schema.tables where table_name like 'table_%' order by table_name limit 1;

It should return the earliest table partition name given the naming conventions we use on them.

Related Question