Partitioning with range types

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Partitioning with range types
Date: 2018-06-15 15:26:44
Message-ID: CAMa1XUiHj4-okAcKJYG3R-nFeP3WB9hL+todwjtgpm7swXmkNg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Several months ago we had some detailed discussions about whether to use
separate date columns to indicate a date range, or to use the daterange
data type. We opted for the latter because this type is specifically
designed for this use case - a table that has a range of valid dates for
the data it contains. It also has some great operators and functions.

But I recently discovered that daterange is not supported in any way as a
partition key because it depends on an expression. I was excited about
this possibility in pg11 with unique constraints on the parent table, but
now it appears it may have instead been to our advantage if we had two
separate date columns instead, so that we could use UPSERT transparently
for date-ranged tables.

Is there any possibility of this feature coming for range types, or, if we
really want to partition using daterange, should we look instead at two
separate date columns?

Thanks,
Jeremy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2018-06-15 15:44:45 Re: Append only replication over intermittent links (with local only delete?)
Previous Message Amit Kapila 2018-06-15 14:36:41 Re: question on streaming replication