I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE.

There are 5 partitions at the moment, containing a span of a month each, starting from FOR VALUES FROM ('2018-01-01') TO ('2018-02-01') and ending with FOR VALUES FROM ('2018-05-01') TO ('2018-06-01').

Most data is entered in a linear and predictable fashion. However, events are consumed by apps who report them, and I do have to allow for a past event to be entered at any time - which could have a timestamp earlier than 2018-01-01, or even a future event (for example a charge that is expected to occur some time in the future).

I was planning on creating one partition for past events that would span much longer than a month since there is no expectation of having too many of those.

I'm not sure what would be the best approach for future events where no partition exists for yet.

Is there a way to get the min/max values I could store within the existing partitions? If not, I could create a reference table that would store these values but I prefer to not have to maintain that.

Should I create a trigger to check for each row being inserted (seems expensive)? Should I capture errors on inserts and deal with those one at a time?

Running on PostgreSQL 10.3.

  • My first idea would be to create future partitions ahead of time. Enough to cover eventualities. (And keep doing so as time advances.) Empty tables are patient and very modest creatures, they'll wait until needed and won't bother you. Commented May 14, 2018 at 15:42
  • Thanks @ErwinBrandstetter. Do you know of a way to get the lower and upper bounds of the range by query? Commented May 14, 2018 at 17:25

1 Answer 1


Is there a way to get the min/max values I could store within the existing partitions?

And you also ask in a comment:

Do you know of a way to get the lower and upper bounds of the range by query?

I wouldn't know of any dedicated system catalog information function for this particular purpose. But:

Range partitioning is based on inheritance internally:

Individual partitions are linked to the partitioned table with inheritance behind-the-scenes;

Inheritance trees are stored in pg_inherits:

one entry for each direct child table

Partition bounds are stored in pg_class.relpartbound in internal format (pg_node_tree).

The system catalog information functions pg_get_expr(pg_node_tree, relation_oid) can:

decompile internal form of an expression

We can build a query from this set of clues. Based on the example for range partitioning in the manual:

SELECT i.inhrelid::regclass
     , partition_bound
     , split_part(partition_bound, '''', 2) AS lower_bound
     , split_part(partition_bound, '''', 4) AS upper_bound
FROM   pg_inherits i
JOIN   pg_class    c ON c.oid = i.inhrelid
     , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
WHERE  inhparent = 'measurement'::regclass;
inhrelid partition_bound lower_bound upper_bound
measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') 2006-02-01 2006-03-01
measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') 2006-03-01 2006-04-01



  • Extracting lower and upper bound from the string based on single quotes is cheap & dirty. There is probably a cleaner way to extract the value from relpartbound directly.
  • Only including the first level of inheritance. You have to walk the graph in pg_inherits recursively to cover sub-partitioning.
  • This builds on several implementation details for declarative partitioning, which is a new feature of Postgres 10. While I do not expect this query to break due to changes in one of the next major versions, there is a chance it might.
  • 2
    Thanks! I've added a CTE around it and took min(lower_bound) and max(upper_bound). I agree with your comment about "cheap & dirty" but it will work for now for what I need. Commented May 15, 2018 at 12:56

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.