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 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?

  • It occured to me this may not be dba-specific, so apologies if this is on the wrong stack site. Jul 9, 2019 at 20:13
  • 2
    Define "earliest". The one with the earliest values in date_column? The one create first? Always disclose your version of Postgres. And an actual table definition would disambiguate which partitioning method is used. Jul 10, 2019 at 0:06
  • @ErwinBrandstetter earliest meaning the oldest table partition. I could search for the oldest date, but that would scan through every partition. I want immediately to access the oldest partition and scan through just that table. Jul 10, 2019 at 18:27
  • Note that generally table names have little relation (outside the designer's mind) to table contents. Nothing, apart from the partition definition, prevents you from storing data from July 2019 in a table named table_2018_01. So, do you use table names as the criteria, or actual partition definitions?
    – mustaccio
    Jul 10, 2019 at 18:33

2 Answers 2


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.


Assuming you have implemented partitioning using Postgres' inheritance, you can use the system column tableoid to do that:

select tableoid::regclass, date_column 
from parted_table
order by date_column
limit 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.